VBA 매크로와 Outlook을 활용해 Excel에서 개인화된 이메일 자동 대량 발송하기

Excel 스프레드시트에 정리된 사용자 명단의 각 사람에게 개인화된 정보를 담아 이메일을 발송해야 하는 업무를 맡게 되었습니다. 여러 방법을 검토한 끝에, 컴퓨터에 설정된 Outlook 프로필을 통해 메일을 보내는 Excel VBA 매크로로 이 작업을 해결했습니다. 이 글에서는 그 전체 과정과 코드를 단계별로 소개합니다.

1. Excel 데이터 준비

먼저 아래와 같은 열 구성의 Excel 파일이 있다고 가정합니다.

이메일 주소 | 성명 | 마지막 비밀번호 변경일 | 계정 상태

2. 이메일 템플릿 설계

이번 예제의 목표는 아래 템플릿을 기반으로 명단의 모든 사용자에게 맞춤형 메일을 보내는 것입니다. %FullUsername%, %status%, %pwdchange% 부분은 각 행의 데이터로 자동 치환됩니다.

제목: woshub.com 도메인 계정 상태 안내
본문: %FullUsername%님께,
woshub.com 도메인의 귀하의 계정은 현재 %status% 상태입니다.
마지막 비밀번호 변경 일시: %pwdchange%

3. VBA 매크로 작성

Excel에서 보기 → 매크로(또는 Alt + F8)를 클릭하고, 매크로 이름을 send_email로 입력한 뒤 만들기를 누릅니다.

VBA 매크로와 Outlook을 활용해 Excel에서 개인화된 이메일 자동 대량 발송하기

VBA 편집기가 열리면 아래 코드를 복사해 붙여넣습니다. 주요 로직에는 설명 주석을 덧붙였습니다. 여기서는 CreateObject("Outlook.Application") 함수를 사용해 VBA 내부에서 Outlook 개체를 생성·제어함으로써 메일 발송을 자동화합니다.

중요: 메일을 보내는 컴퓨터에 Outlook 프로필이 미리 구성되어 있어야 하며, 발송되는 모든 메일은 해당 사서함(이메일 주소)을 통해 전송됩니다.

Sub send_email()

    Dim olApp As Object

    Dim olMailItm As Object

    Dim iCounter As Integer

    ' 제목

    strSubj = "woshub.com 도메인 계정 상태 안내"

    On Error GoTo dbg

    ' 새 Outlook 개체 생성

    Set olApp = CreateObject("Outlook.Application")

    ' A열에 데이터가 있는 행 수만큼 반복

    For iCounter = 1 To WorksheetFunction.CountA(Columns(1))

        ' Outlook에 새 메일 항목 생성

        Set olMailItm = olApp.CreateItem(0)

        strBody = ""

        useremail = Cells(iCounter, 1).Value     ' A열: 이메일 주소

        FullUsername = Cells(iCounter, 2).Value  ' B열: 성명

        Status = Cells(iCounter, 4).Value        ' D열: 계정 상태

        pwdchange = Cells(iCounter, 3).Value     ' C열: 비밀번호 변경일

        ' 메일 본문 작성

        strBody = FullUsername & "님께," & vbCrLf

        strBody = strBody & "woshub.com 도메인의 귀하의 계정은 현재 " & Status & " 상태입니다." & vbCrLf

        strBody = strBody & "마지막 비밀번호 변경 일시: " & pwdchange & vbCrLf

        olMailItm.To = useremail

        olMailItm.Subject = strSubj

        olMailItm.BodyFormat = 1  ' 1 = 텍스트 형식, 2 = HTML 형식

        olMailItm.Body = strBody

        olMailItm.Send  ' 즉시 발송 (테스트 시 .Send 대신 .Display 사용 권장)

        Set olMailItm = Nothing

    Next iCounter

    Set olApp = Nothing

dbg:

    ' 오류가 있으면 화면에 표시

    If Err.Description <> "" Then MsgBox Err.Description

End Sub

4. 매크로 저장 및 실행

코드 입력이 끝나면 Excel 파일을 .xlsm(매크로 포함 통합 문서) 형식으로 반드시 저장해야 합니다. 이후 보기 → 매크로에서 send_email을 선택하고 실행을 클릭합니다.

VBA 매크로와 Outlook을 활용해 Excel에서 개인화된 이메일 자동 대량 발송하기

매크로는 워크시트의 모든 행을 한 줄씩 순회하면서 수신자별로 메일을 생성하고 즉시 발송합니다. 명단에 수십~수백 명이 있어도 몇 초 만에 발송이 완료됩니다.

활용 시 알아두면 좋은 팁

발송 전 테스트: 잘못된 내용을 대량 발송하는 실수를 방지하려면, 처음에는 olMailItm.Send 대신 olMailItm.Display를 사용해 메일 창만 띄워 내용을 확인하는 것이 안전합니다.
HTML 서식 활용: BodyFormat을 2로 설정하고 .Body 대신 .HTMLBody 속성에 HTML 문자열을 넣으면 굵은 글씨, 색상, 링크 등이 적용된 서식 있는 메일을 보낼 수 있습니다.
Outlook 보안 설정: 조직 정책에 따라 외부 프로그램의 메일 발송 시도 시 경고 창이 나타날 수 있습니다. 발송 건수가 많다면 각 메일 사이에 약간의 지연을 두거나 관리자에게 허용을 요청하는 것이 좋습니다.
데이터 점검: 실행 전에 이메일 주소 오타나 빈 셀이 없는지 확인하세요. 빈 셀이 있으면 해당 행에서 오류가 발생할 수 있습니다.

이처럼 VBA 매크로와 Outlook을 연동하면 별도의 프로그램 설치 없이 Excel 명단만으로 개인화된 이메일을 손쉽게 자동 발송할 수 있습니다. 공지 메일, 계정 안내, 리포트 배포 등 반복적인 메일 업무에 유용하게 활용해 보세요.