엑셀 VBA로 자동 이메일 발송하기 - 완벽 가이드

 

개요

엑셀에서 VBA 매크로를 사용하여 Outlook을 통해 대량의 이메일을 자동으로 발송하는 방법을 소개합니다. 이 코드는 실무에서 바로 활용 가능하며, 발송 결과를 자동으로 기록하고 오류 처리까지 완벽하게 구현되어 있습니다.

주요 기능

• ✅ 대량 이메일 자동 발송: 여러 건의 메일을 한 번에 발송
• ✅ 첨부파일 지원: 각 메일마다 개별 첨부파일 추가 가능
• ✅ 발송 결과 자동 기록: 성공/실패 여부와 시간을 자동으로 엑셀에 기록
• ✅ 에러 처리: 발송 실패 시에도 다음 메일 계속 발송
• ✅ 진행 상황 표시: 상태 표시줄에 실시간 진행률 표시
• ✅ CC 기능: 참조 수신자 추가 가능

엑셀 시트 구조

Mail_Send라는 이름의 시트를 생성하고 다음과 같이 구성하세요:

A열 (To)B열 (CC)C열 (Subject)D열 (Body)E열 (Status)F열 (Attachment)

수신자 이메일

참조 이메일

제목

본문

발송상태

첨부파일 경로

예시:

• A2: example@company.com
• B2: cc@company.com (선택사항)
• C2: 11월 업무 보고서 제출 안내
• D2: 안녕하세요. 11월 업무 보고서를 제출해 주시기 바랍니다.
• E2: (비워둠 - 자동으로 채워짐)
• F2: C:\Documents\report.pdf (선택사항)

VBA 코드

Sub SendEmail_Complete()     On Error GoTo ErrorHandler          Dim OutlookApp As Object     Dim OutlookMail As Object     Dim ws As Worksheet     Dim LastRow As Long     Dim i As Long     Dim SuccessCount As Long     Dim FailCount As Long          ' 초기화     SuccessCount = 0     FailCount = 0          Set ws = ActiveWorkbook.Sheets("Mail_Send")     LastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row          ' 데이터가 없는 경우 처리     If LastRow < 2 Then         MsgBox "발송할 데이터가 없습니다.", vbExclamation         Exit Sub     End If          ' 사용자 확인     If MsgBox("총 " & LastRow - 1 & "건의 메일을 발송하시겠습니까?", vbYesNo + vbQuestion, "메일 발송 확인") = vbNo Then         Exit Sub     End If          Set OutlookApp = CreateObject("Outlook.Application")          ' 진행 상황 표시를 위한 상태 표시줄 사용     Application.ScreenUpdating = False          For i = 2 To LastRow         Application.StatusBar = "메일 발송 중... (" & i - 1 & "/" & LastRow - 1 & ")"                  On Error Resume Next                  If ws.Cells(i, 1).Value <> "" Then             Set OutlookMail = OutlookApp.CreateItem(0)                          With OutlookMail                 .To = ws.Cells(i, 1).Value                 .CC = ws.Cells(i, 2).Value ' CC (선택사항)                 .Subject = ws.Cells(i, 3).Value                 .Body = ws.Cells(i, 4).Value                                  ' 첨부파일이 있는 경우                 If ws.Cells(i, 6).Value <> "" Then                     If Dir(ws.Cells(i, 6).Value) <> "" Then                         .Attachments.Add ws.Cells(i, 6).Value                     End If                 End If                                  .Send             End With                          If Err.Number = 0 Then                 ws.Cells(i, 5).Value = "발송완료 - " & Format(Now, "yyyy-mm-dd hh:mm:ss")                 ws.Cells(i, 5).Interior.Color = RGB(144, 238, 144)                 SuccessCount = SuccessCount + 1             Else                 ws.Cells(i, 5).Value = "발송실패 - " & Err.Description                 ws.Cells(i, 5).Interior.Color = RGB(255, 182, 193)                 FailCount = FailCount + 1                 Err.Clear             End If                          Set OutlookMail = Nothing         End If                  On Error GoTo ErrorHandler                  ' 서버 부하 방지를 위한 짧은 대기 시간         Application.Wait (Now + TimeValue("0:00:01"))     Next i          ' 정리     Application.ScreenUpdating = True     Application.StatusBar = False          Set OutlookApp = Nothing     Set ws = Nothing          ' 결과 메시지     MsgBox "메일 발송이 완료되었습니다!" & vbCrLf & vbCrLf & _            "성공: " & SuccessCount & "건" & vbCrLf & _            "실패: " & FailCount & "건", vbInformation, "발송 완료"          Exit Sub ErrorHandler:     Application.ScreenUpdating = True     Application.StatusBar = False     MsgBox "오류가 발생했습니다: " & Err.Description, vbCritical          Set OutlookMail = Nothing     Set OutlookApp = Nothing     Set ws = Nothing End Sub

 

사용 방법

1. VBA 편집기 열기

• Alt + F11을 눌러 VBA 편집기를 엽니다.

2. 모듈 추가

• 메뉴에서 삽입 > 모듈을 선택합니다.

3. 코드 붙여넣기

• 위의 코드를 복사하여 모듈에 붙여넣습니다.

4. 엑셀 데이터 준비

• Mail_Send 시트에 발송할 이메일 정보를 입력합니다.

5. 매크로 실행

• Alt + F8을 누르고 SendEmail_Complete를 선택하여 실행합니다.

코드 상세 설명

발송 전 확인 기능

 

 

vba

If MsgBox("총 " & LastRow - 1 & "건의 메일을 발송하시겠습니까?", vbYesNo + vbQuestion) = vbNo Then
    Exit Sub
End If

실수로 대량 발송하는 것을 방지하기 위한 확인 메시지를 표시합니다.

진행 상황 표시

 

 

vba

Application.StatusBar = "메일 발송 중... (" & i - 1 & "/" & LastRow - 1 & ")"

엑셀 하단 상태 표시줄에 현재 진행률을 실시간으로 표시합니다.

발송 결과 기록

성공 시:

• E열에 "발송완료" + 시간 기록
• 셀 배경색을 연두색으로 표시

실패 시:

• E열에 "발송실패" + 오류 메시지 기록
• 셀 배경색을 분홍색으로 표시

서버 부하 방지

 

 

vba

Application.Wait (Now + TimeValue("0:00:01"))

각 메일 발송 후 1초 대기하여 서버 부하를 방지합니다.

주의사항

1. Outlook 설치 필수: 이 코드는 Outlook을 통해 메일을 발송하므로 Outlook이 설치되어 있어야 합니다.
2. 메일 서버 설정: Outlook이 메일 계정과 연결되어 있어야 합니다.
3. 첨부파일 경로: 첨부파일은 절대 경로로 입력해야 하며, 파일이 실제로 존재해야 합니다.
4. 매크로 보안 설정: 매크로가 실행되지 않으면 파일 > 옵션 > 보안 센터 > 매크로 설정에서 허용해야 합니다.
5. 대량 발송 시: 일반적으로 하루 발송 제한이 있을 수 있으므로 대량 발송 시 주의하세요.

커스터마이징 팁

HTML 형식 본문 사용

.Body 대신 .HTMLBody를 사용하면 HTML 형식의 메일을 발송할 수 있습니다:

 

 

vba

.HTMLBody = ws.Cells(i, 4).Value

대기 시간 조정

서버 상황에 맞게 대기 시간을 조절할 수 있습니다:

 

 

vba

Application.Wait (Now + TimeValue("0:00:02")) ' 2초로 변경

시트 이름 변경

다른 시트 이름을 사용하려면 해당 부분을 수정하세요:

 

 

vba

Set ws = ActiveWorkbook.Sheets("원하는시트이름")

활용 사례

• 📧 고객 대상 프로모션 메일 발송
• 📊 월간 보고서 자동 배포
• 🎓 교육 자료 일괄 전송
• 💼 업무 공지사항 전달
• 🎂 생일 축하 메시지 자동 발송

문제 해결

"Mail_Send 시트를 찾을 수 없습니다" 오류

• 엑셀 파일에 Mail_Send라는 정확한 이름의 시트가 있는지 확인하세요.

첨부파일이 첨부되지 않음

• 파일 경로가 정확한지 확인하세요.
• 역슬래시(\)를 사용해야 합니다.
• 파일이 실제로 존재하는지 확인하세요.

Outlook이 열리지 않음

• Outlook이 설치되어 있는지 확인하세요.
• Outlook을 한 번 수동으로 실행해보세요.

마치며

이 VBA 매크로를 사용하면 반복적인 이메일 발송 업무를 자동화하여 업무 효율을 크게 높일 수 있습니다. 코드를 자신의 환경에 맞게 수정하여 활용해보세요!

 

엑셀VBA로 대량 이메일 보내기 자동화 매크로.xlsm

0.02MB