엑셀 VBA 매크로와 Outlook으로 자동 이메일 발송하기
엑셀 시트에 정리된 사용자 목록의 각 사람에게 개인화된 정보가 담긴 이메일을 발송해야 하는 업무를 맡게 되었습니다. 엑셀의 VBA 매크로를 활용하면 컴퓨터에 설정된 Outlook 프로필을 통해 이메일을 자동으로 보낼 수 있습니다. 이번 글에서는 실제로 사용한 해결 방법을 단계별로 소개합니다.
1. 엑셀 데이터 구조 준비
먼저 다음과 같은 열로 구성된 엑셀 파일이 있다고 가정해 보겠습니다.
Email | Full Name | Last Password Change Date | Account status
즉, 이메일 주소, 사용자 이름, 마지막 비밀번호 변경일, 계정 상태 정보가 각 행에 저장되어 있는 형태입니다.
2. 발송할 이메일 템플릿 작성
목표는 아래 템플릿을 기반으로 목록의 모든 사용자에게 맞춤형 이메일을 보내는 것입니다.
제목: woshub.com 도메인 계정 상태 안내
본문: %FullUsername%님, 안녕하세요.
woshub.com 도메인의 귀하의 계정은 현재 %status% 상태입니다.
마지막 비밀번호 변경 일시: %pwdchange%
%FullUsername%, %status%, %pwdchange% 부분은 엑셀 시트의 각 행 데이터로 자동 치환됩니다.
3. VBA 매크로 생성하기
엑셀에서 보기(View) → 매크로(Macros) 메뉴로 이동합니다. 매크로 이름을 send_email로 지정하고 만들기(Create)를 클릭합니다.
4. VBA 코드 입력
열린 VBA 편집기에 아래 코드를 복사해 붙여넣습니다. 이메일 발송을 자동화하기 위해 VBA 스크립트 안에서 Outlook 객체를 생성하고 사용할 수 있게 해주는 CreateObject("Outlook.Application") 함수를 사용했습니다.
중요: 이메일을 발송하는 컴퓨터에 Outlook 프로필이 반드시 설정되어 있어야 합니다. 실제 발송에는 이 사서함(및 해당 이메일 주소)이 사용됩니다.
Sub send_email()
Dim olApp As Object
Dim olMailItm As Object
Dim iCounter As Integer
Dim Dest As Variant
Dim SDest As String
' 이메일 제목
strSubj = "Your account status on woshub.com domain"
On Error GoTo dbg
' 새 Outlook 객체 생성
Set olApp = CreateObject("Outlook.Application")
For iCounter = 1 To WorksheetFunction.CountA(Columns(1))
' Outlook에서 새 항목(이메일) 생성
Set olMailItm = olApp.CreateItem(0)
strBody = ""
useremail = Cells(iCounter, 1).Value
FullUsername = Cells(iCounter, 2).Value
Status = Cells(iCounter, 4).Value
pwdchange = Cells(iCounter, 3).Value
' 이메일 본문 작성
strBody = "Dear " & FullUsername & vbCrLf
strBody = strBody & " Your account in woshub.com domain is in" & Status & " state" & vbCrLf
strBody = strBody & "The date and time of the last password change is" & pwdchange & vbCrLf
olMailItm.To = useremail
olMailItm.Subject = strSubj
olMailItm.BodyFormat = 1
' 1 - 텍스트 형식, 2 - HTML 형식
olMailItm.Body = strBody
olMailItm.Send
Set olMailItm = Nothing
Next iCounter
Set olApp = Nothing
dbg:
' 오류가 있는 경우 화면에 표시
If Err.Description <> "" Then MsgBox Err.Description
End Sub
코드 주요 동작 설명
- WorksheetFunction.CountA(Columns(1))로 첫 번째 열(이메일 주소 열)에 데이터가 있는 행 수를 세어 반복 범위를 정합니다.
- 각 행에서 이메일 주소, 이름, 계정 상태, 비밀번호 변경일을 순서대로 읽어옵니다.
- olMailItm.BodyFormat = 1은 텍스트 형식이며, 2로 변경하면 HTML 형식으로 발송됩니다.
- olMailItm.Send가 호출되는 즉시 해당 이메일이 발송됩니다.
5. 매크로 실행하기
작성이 끝나면 엑셀 파일을 .xlsm(매크로 지원 통합 문서) 형식으로 저장합니다. 이후 매크로 목록에서 생성한 프로시저(send_email)를 선택하고 실행(Run)을 클릭하면 됩니다.
매크로는 워크시트의 모든 행을 하나씩 순회하면서 목록에 있는 각 수신자에게 이메일을 생성하고 발송합니다.
마무리 및 활용 팁
이 방법을 활용하면 수백 명의 사용자에게도 몇 분 만에 개인화된 이메일을 보낼 수 있습니다. 실무에 적용할 때는 다음 사항을 참고하면 좋습니다.
- 대량 발송 전에 메일 서버의 발송량 제한 정책을 미리 확인하세요.
- BodyFormat = 2와 HTMLBody 속성을 사용하면 서식이 적용된 이메일을 보낼 수 있습니다.
- 실제 발송 전에는 olMailItm.Send 대신 olMailItm.Display를 사용해 결과물을 먼저 확인하는 것이 안전합니다.
- Outlook 보안 설정에 따라 외부 프로그램이 이메일을 보낼 때 경고 창이 표시될 수 있으므로 사전에 정책을 점검하세요.