엑셀 자동 입력 + 메일 발송 | ChatGPT로 자동화 폼 만들기
엑셀 자동 입력 + 메일 발송 목차 바로가기
영상 강의
예제파일 다운로드
오빠두엑셀의 강의 예제파일은 여러분을 위해 자유롭게 제공하고 있습니다.
- [실무기초] 엑셀 데이터 자동 입력 + 메일 발송 폼 만들기예제파일
라이브 강의 전체영상도 함께 확인해보세요!
위캔두 회원이 되시면 매주 오빠두엑셀에서 진행하는 라이브강의 풀영상을 확인하실 수 있습니다.
엑셀 양식 컨트롤 추가하기
엑셀로 제작한 기본 서식에 양식컨트롤을 추가하면 사용자 친화적이고 자료를 쉽게 입력할 수 있는 '폼' 형식의 서식을 만들 수 있습니다. 오늘은 엑셀 초보자도 쉽게 사용할 수 있는 양식 컨트롤의 기본 사용법을 알아보겠습니다.
- 영상 강의를 참고해 자동 입력폼에 사용할 서식을 만들거나, 예제파일에 함께 올려드린 '서식완성' 파일을 실행해 양식 컨트롤 실습을 진행합니다. 먼저 엑셀 탭 목록에서 [개발도구] 탭을 확인합니다. 만약 개발도구 탭이 보이지 않는다면 리본 메뉴를 우클릭 - 리본 메뉴 사용자 지정에서 [개발도구]를 체크하고 '확인' 버튼을 클릭해 개발도구를 활성화합니다.
개발도구 탭을 활성화합니다. - [개발도구] 탭에서 [삽입] - 양식 컨트롤에서 [스핀 단추] 를 선택하고 원하는 위치에 적절히 드래그해서 추가합니다.
양식 컨트롤 - 스핀 단추를 추가합니다. 오빠두Tip : Alt 키를 누르고 드래그하면 셀 너비에 맞춰서 스핀단추를 추가할 수 있습니다. - 추가한 스핀 단추를 우클릭 - [컨트롤 서식] 으로 이동한 후, 셀 연결 입력창을 선택하고 연결할 셀을 선택합니다. 이번 예제에서는 동반 인원수를 입력할 C12셀을 선택합니다.
셀 연결 입력창에 스핀 단추와 연결할 셀을 선택합니다. - 그리고 최소값과 최대값을 각각 0, 5로 입력하고 [확인] 버튼을 클릭해 설정을 종료합니다. 이제 스핀 단추를 클릭해 동반 인원수를 편리하게 변경할 수 있습니다.
스핀 단추의 최소, 최대값을 변경 후 설정을 저장합니다. - 이제 라디오 버튼(옵션 단추)를 추가합니다. [개발도구] 탭 - [삽입] - [양식 컨트롤] 에서 옵션단추를 선택한 후, 원하는 위치에 적절히 드래그해서 추가합니다.
양식 컨트롤에서 옵션 단추를 추가합니다. - 추가한 옵션 단추를 우클릭 - '텍스트 편집'을 클릭하고 옵션 단추의 텍스트를 "주차 필요"로 변경합니다.
옵션 단추의 텍스트를 변경합니다. - 옵션 단추를 그냥 클릭하면 옵션이 체크되기만 할 뿐 개체를 선택할 수 없습니다. 그럴 경우, 옵션 단추를 우클릭하면 개체를 선택할 수 있습니다. 옵션 단추를 선택한 후 복사해서 붙여넣고, 새로운 옵션단추의 텍스트를 "필요 없음"으로 변경합니다.
옵션 단추를 하나 더 추가합니다. - 옵션 단추를 모두 추가했으면, 옵션 단추와 연결할 셀을 선택합니다. 옵션 단추를 우클릭 - 컨트롤 서식으로 이동한 후, 연결할 셀로 E26셀을 선택하고 [확인] 버튼을 클릭해 설정을 마칩니다.
옵션 단추와 연결할 셀을 선택합니다. - 스핀 단추와 옵션 단추를 모두 추가하면 아래 그림과 같이 버튼을 클릭해서 편하게 값을 입력할 수 있는 신청서 폼이 완성됩니다.
양식 컨트롤을 사용한 신청서 폼이 완성됩니다. 오빠두Tip : 전체 서식을 제작하는 과정은 영상 강의에서 알기 쉽게 정리했습니다.
ChatGTP로 엑셀 자동 입력 매크로 만들기
이제 [신청서] 시트에 입력한 값을 [신청 목록] 시트에 자동으로 입력하는 매크로를 작성해보겠습니다. 요즘은 AI가 많이 발전해서, 복잡한 매크로를 직접 작성하지 않고 간단한 프롬프트로 누구나 쉽게 코드를 생성할 수 있습니다.
- 예제파일에서 프롬프트 가이드 워드 파일을 실행한 후, 첫번째 항목인 '입력폼의 값을 DB에 추가하기' 프롬프트를 복사합니다. 아래 프롬프트는 예제파일 기준으로 작성되었으며, 실무에서는 필요에 따라 아래 프롬프트에서 빨간색으로 체크해드린 부분을 수정해 사용해보시길 바랍니다.
== [핵심지침] 시작 ==
- 당신은 지금부터 엑셀 VBA 매크로 전문가로서, 아래 요청한 작업에 필요한 매크로 코드를 작성합니다.
- 작성한 VBA 코드를 초보자도 쉽게 이해할 수 있도록, 작성된 코드에는 모든 주요 단계 및 주석을 꼼꼼하게 친절하고 추가하세요.
- 주석은 코드의 목적, 실행 방법, 작동 원리를 간결하고 명확하게 설명합니다.
- 모든 엑셀 버전에서 문제없이 동작할 수 있도록 Late Binding 방식으로 작성하세요.
- 외부 라이브러리가 필요한 경우, 참조 설정 없이도 작동할 수 있도록 코드에 포함합니다.
- 만약 실행 중 오류가 발생할 가능성이 있는 경우, MsgBox를 통해 오류 메시지를 사용자에게 안내하도록 합니다.
== [핵심지침] 끝 ==
== [요청] 시작 ==
현재 실행중인 통합문서에서 [신청서] 시트의 C19:C26 범위 데이터를 [신청목록] 시트의 B:I 열에 누적해서 추가하는 매크로를 작성하려고 해.
현재 1행에는 머리글이 작성되어 있어. 그리고 데이터를 추가한 후, A열에는 1씩 증가하는 ID 순번도 함께 관리할거야.
매크로를 실행하면 [신청서] 시트에서 C4,F4,C6,F6,C8,C10,C12 셀 중 하나라도 비어있으면 ‘모든 필드를 올바르게 입력하세요’ 라고 안내창을 띄운 후 매크로를 종료해.
모든 필드가 잘 입력되었으면, C19:C26 범위 데이터를 [신청목록] 시트 맨 아래에 새로운 레코드로 추가하는 매크로 코드를 작성해.
매크로 동작 완료 안내창 코드는 내가 직접 추가할게. 오류가 발생할 때에만 안내창을 띄우도록 작성해.
==[요청] 끝 ==
자, 이제 코드 작성을 시작하세요. - 복사한 프롬프트를 ChatGPT 나 다른 AI 모델의 입력창에 붙여넣고 실행하면 아래 그림과 같이 매크로가 작성됩니다. [복사] 버튼을 클릭해서 자동된 매크로를 복사합니다.
프롬프트 실행 후, 작성된 매크로를 복사합니다. - 다시 엑셀로 돌아온 후, [개발도구] 탭 - [Visual Basic] 버튼을 클릭하거나 Alt + F11 을 눌러 매크로 편집기를 실행한 후, [삽입] 탭 - [모듈]을 클릭해 새 모듈을 추가합니다.
매크로 편집기 - 삽입 - 모듈을 추가합니다. - 추가한 모듈 안에 복사한 코드를 붙여넣고, 코드 이름을 변경합니다. 이번 에제에서는 '데이터옮기기'로 변경했습니다.
완성된 코드를 모듈 안에 붙여넣고, 코드 이름을 변경합니다. - 엑셀 시트에서 버튼으로 사용할 도형을 우클릭 - [매크로 지정]으로 이동한 후, 방금 추가했던 '데이터 옮기기' 매크로를 지정합니다.
버튼을 우클릭 - 매크로 지정에서 실행한 매크로를 선택합니다. - 이제 이름, 회사명, 직책, 부서 등을 입력하고 버튼을 클릭하면 [신청서]에 입력한 데이터가 [신청 목록]으로 옮겨지는 것을 확인할 수 있습니다.
버튼을 클릭하면 신청서의 데이터가 신청 목록으로 복사됩니다.
폼 초기화 매크로 만들기
이제 입력을 마친 후, 폼을 깔끔하게 정리하는 초기화 매크로를 작성하겠습니다.
- 아래 프롬프트를 복사해 ChatGPT 입력창에 붙여넣고 실행하면 폼 초기화 매크로가 생성됩니다.
== [핵심지침] 시작 ==
- 당신은 지금부터 엑셀 VBA 매크로 전문가로서, 아래 요청한 작업에 필요한 매크로 코드를 작성합니다.
- 작성한 VBA 코드를 초보자도 쉽게 이해할 수 있도록, 작성된 코드에는 모든 주요 단계 및 주석을 꼼꼼하게 친절하고 추가하세요.
- 주석은 코드의 목적, 실행 방법, 작동 원리를 간결하고 명확하게 설명합니다.
- 모든 엑셀 버전에서 문제없이 동작할 수 있도록 Late Binding 방식으로 작성하세요.
- 외부 라이브러리가 필요한 경우, 참조 설정 없이도 작동할 수 있도록 코드에 포함합니다.
- 만약 실행 중 오류가 발생할 가능성이 있는 경우, MsgBox를 통해 오류 메시지를 사용자에게 안내하도록 합니다.
== [핵심지침] 끝 ==
== [요청] 시작 ==
현재 실행중인 통합문서의 [신청서] 시트에서 C4, F4, C6, F6, C8, C10 셀의 값을 지우고, C12 셀의 값을 0으로 바꾸는 매크로를 작성해.
값을 지우는 범위는 셀 병합이 되어 있을 수 있으니, 범위를 먼저 Select 한 후 Selection 의 값을 지우는 방식으로 코드를 작성해.
매크로 동작 완료 안내창 코드는 내가 직접 추가할게. 오류가 발생할 때에만 안내창을 띄우도록 작성해.
==[요청] 끝 ==
자, 이제 코드 작성을 시작하세요. - 완성된 코드를 이전에 추가했던 모듈 아래에 붙여넣고, 코드의 이름을 "폼초기화"로 변경합니다.
완성된 코드를 붙여넣은 후, 코드 이름을 변경합니다. - 이제 엑셀 시트에서 버튼으로 사용할 도형을 우클릭 - [매크로 지정]에서 "폼초기화" 매크로를 지정한 후, 버튼을 클릭하면 신청서에 작성한 값이 올바르게 초기화 되는 것을 확인할 수 있습니다.
매크로를 실행하면 신청서의 폼이 초기화됩니다.
메일 발송 매크로 만들기
이제 마지막 단계로 엑셀에 입력한 데이터를 기반으로 이메일까지 자동으로 발송하는 매크로를 작성하겠습니다. 오늘은 네이버와 G메일에서 무료로 제공되는 SMTP 기능을 사용하며, 네이버 메일을 기준으로 실습을 진행합니다. G메일의 SMTP 설정방법은 함께 첨부해드린 PDF 파일을 참고하시길 바랍니다. 2025년 7월 기준, 네이버는 시간당 최대 30통으로 발송이 제한되며, G메일은 하루 최대 500통으로 발송이 제한됩니다.
- 네이버 메일에서 [환경설정] - [POP3/IMAP 설정]을 클릭한 후, POP3/SMTP 설정을 "사용함"으로 바꾸고 원본 저장은 '네이버 메일에 원본 저장'을 선택한 후 [저장] 버튼을 클릭합니다.
네이버 메일에서 SMTP 설정을 변경합니다. - 25년 7월 기준 네이버 보안이 강화되어 SMTP를 사용하려면 2단계 인증을 반드시 활성화해야 합니다. 2단계 인증을 켠 후, 내 계정 - [보안 설정] - [관리] 로 이동합니다.
네이버 2단계 인증을 켠 후, 보안 설정 - 관리로 이동합니다. - 애플리케이션 비밀번호 관리에서 종류로 "SMTP"를 입력한 후, [생성하기] 버튼을 클릭하면 SMTP 발송용 애플리케이션 비밀번호가 생성됩니다. 생성된 비밀번호를 복사합니다.
애플리케이션 비밀번호 관리에서 임시 비밀번호를 생성합니다. - 생성된 비밀번호를 아래 프롬프트에 붙여넣은 후, ChatGPT에서 프롬프트를 실행하면 엑셀에 입력된 데이터를 참고해 메일을 발송하는 매크로가 작성됩니다.
== [핵심지침] 시작 ==
- 당신은 지금부터 엑셀 VBA 매크로 전문가로서, 아래 요청한 작업에 필요한 매크로 코드를 작성합니다.
- 작성한 VBA 코드를 초보자도 쉽게 이해할 수 있도록, 작성된 코드에는 모든 주요 단계 및 주석을 꼼꼼하게 친절하고 추가하세요.
- 주석은 코드의 목적, 실행 방법, 작동 원리를 간결하고 명확하게 설명합니다.
- 모든 엑셀 버전에서 문제없이 동작할 수 있도록 Late Binding 방식으로 작성하세요.
- 외부 라이브러리가 필요한 경우, 참조 설정 없이도 작동할 수 있도록 코드에 포함합니다.
- 만약 실행 중 오류가 발생할 가능성이 있는 경우, MsgBox를 통해 오류 메시지를 사용자에게 안내하도록 합니다.
== [핵심지침] 끝 ==
== [요청] 시작 ==
엑셀 CDO 개체를 활용해 이메일을 보내는 매크로를 작성하려고 해.
SMTP 프로토콜을 사용할거고, 포트는 465(SSL)을 쓸거야. 사용자 ID와 비밀번호는 다음과 같아.
사용자 ID : abc@naver.com
비밀번호 : 임시비밀번호이메일 발송에 사용할 정보는 현재 실행중인 통합문서의 [신청서] 시트에 작성되어 있어. 각 셀에 작성된 정보는 다음과 같아.
C4 : 이름
F4 : 회사명
C6 : 부서
F6 : 직책
C8 : 이메일 (받을사람)
C10: 참석세션
C12 : 동반인원수
C26 : 주차여부위 정보를 참고해서 마이크로소프트 AI TOUR 의 참가자 등록이 성공적으로 완료 되었다는 HTML 이메일 본문과 함께 받을 사람 이메일 주소로 메일을 발송하는 매크로 코드를 작성해. 이메일의 HTML 본문에는 <style> 태그 적용이 안되니, 인라인으로 스타일을 직접 넣어서 작성해.
==[요청] 끝 ==
자, 이제 코드 작성을 시작하세요. - 이전과 동일한 방법으로 완성된 코드를 복사해 모듈 아래에 붙여넣은 후, 코드 이름을 "메일발송"으로 변경합니다.
완성된 코드를 붙여넣고 코드 이름을 변경합니다. - 이제 엑셀 시트에서 버튼을 클릭했을 때 실행할 매크로를 "메일발송"으로 변경한 후, 신청서에 값을 입력하고 버튼을 클릭하면 메일이 올바르게 발송되는 것을 확인할 수 있습니다.
매크로를 실행하면 신청서의 데이터를 참고해 메일이 발송됩니다. 오빠두Tip : 아웃룩을 활용해 메일 발송을 자동화하는 방법은 이전 영상 강의를 참고하세요!👇
- 모든 매크로가 잘 동작하는 것을 확인했으면 "데이터 입력 → 메일 발송 → 폼 초기화"로 이어지는 최종 매크로를 추가합니다. 모듈의 맨 위에 새로운 줄을 추가한 후, "Sub 최종매크로"를 입력하고 엔터키로 실행하면 아래 그림과 같이 "최종매크로" 매크로 코드가 추가됩니다.
작업을 순차적으로 실행하는 최종 매크로를 작성합니다. - 최종매크로 코드는 아래와 같이 "데이터 입력 → 메일 발송 → 폼 초기화"가 차례대로 실행되도록 작성하고, 그 아래로는 신청 완료 알림까지 띄우도록 메세지 박스를 추가합니다.
Sub 최종매크로()
데이터옮기기
메일발송
폼초기화
MsgBox "마이크로소프트 AI 투어 신청이 완료되었습니다."
End Sub
최종 매크로를 작성합니다. - 마지막 단계로 엑셀 시트에서 버튼을 클릭했을 때 실행한 매크로를 "최종매크로"로 변경한 후, 버튼을 클릭하면 데이터 입력과 메일 발송까지 잘 동작하는 것을 확인할 수 있습니다.
버튼을 클릭했을 때 실행할 매크로로 '최종 매크로'를 선택하면 자동화 폼이 완성됩니다.
- 영상 강의를 참고해 자동 입력폼에 사용할 서식을 만들거나, 예제파일에 함께 올려드린 '서식완성' 파일을 실행해 양식 컨트롤 실습을 진행합니다. 먼저 엑셀 탭 목록에서 [개발도구] 탭을 확인합니다. 만약 개발도구 탭이 보이지 않는다면 리본 메뉴를 우클릭 - 리본 메뉴 사용자 지정에서 [개발도구]를 체크하고 '확인' 버튼을 클릭해 개발도구를 활성화합니다.
