VBA 스크립트로 엑셀(Excel)에서 이메일 자동 발송하는 방법

Microsoft Excel에서 이메일을 보내는 데는 몇 줄의 간단한 스크립트만 있으면 충분합니다. 스프레드시트에 이 기능을 추가하면 엑셀로 처리할 수 있는 업무의 범위가 크게 넓어집니다.

프로그래밍 지식 없이도 VBA 스크립트와 동일한 작업을 수행할 수 있는 다양한 엑셀 매크로를 소개해 왔지만, PC 정보 전체를 담은 스프레드시트 보고서를 생성하는 것처럼 VBA로만 가능한 고급 기능도 여전히 많이 있습니다.

엑셀에서 이메일을 보내야 하는 이유

엑셀 안에서 직접 이메일을 보내고 싶은 이유는 다양합니다.

예를 들어, 직원들이 매주 문서나 스프레드시트를 업데이트할 때 업데이트가 완료되었다는 이메일 알림을 받고 싶을 수 있습니다. 또는 연락처 스프레드시트가 있고, 모든 연락처에 한 번에 이메일을 발송하고 싶을 수도 있습니다.

엑셀에서 이메일 발송을 스크립팅하는 일이 복잡할 것이라고 생각하실 수 있지만, 전혀 그렇지 않습니다.

이 글에서 소개하는 방법은 오랫동안 Excel VBA에서 제공되어 온 CDO(Collaboration Data Objects) 기능을 활용합니다.

CDO는 Windows 초창기 버전부터 사용되어 온 메시징 컴포넌트입니다. 원래 CDONTS라고 불렸으며, Windows 2000과 XP의 등장과 함께 "CDO for Windows 2000"으로 대체되었습니다. 이 컴포넌트는 Microsoft Word나 Excel의 VBA 환경에 이미 포함되어 있어 별도 설치 없이 바로 사용할 수 있습니다.

이 컴포넌트를 활용하면 Windows 제품에서 VBA로 이메일을 보내는 작업이 매우 간단해집니다. 이번 예제에서는 Excel의 CDO 컴포넌트를 사용해 특정 엑셀 셀의 결과값을 담은 이메일을 발송해 보겠습니다.

1단계: VBA 매크로 만들기

첫 번째 단계는 Excel의 개발 도구(Developer) 탭으로 이동하는 것입니다.

개발 도구 탭에서 컨트롤(Controls) 그룹의 삽입(Insert)을 클릭한 후 명령 단추(Command Button)를 선택합니다.

시트 위에 단추를 그린 다음, 리본 메뉴의 매크로(Macros)를 클릭하여 새 매크로를 만듭니다.

만들기(Create) 버튼을 클릭하면 VBA 편집기가 열립니다.

편집기에서 도구(Tools) > 참조(References)로 이동하여 CDO 라이브러리에 대한 참조를 추가합니다.

목록을 아래로 스크롤하여 Microsoft CDO for Windows 2000 Library를 찾은 뒤, 체크박스에 표시하고 확인(OK)을 클릭합니다.

확인을 클릭한 후에는 스크립트를 붙여넣을 함수의 이름을 반드시 기억해 두세요. 나중에 단추와 연결할 때 필요합니다.

2단계: CDO '보내는 사람' 및 '받는 사람' 필드 설정

먼저 메일 개체를 생성하고, 이메일 발송에 필요한 모든 필드를 설정해야 합니다.

대부분의 필드는 선택 사항이지만, 보내는 사람(From)과 받는 사람(To) 필드는 반드시 입력해야 한다는 점에 유의하세요.

Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config As Variant
Dim strSubject As String
Dim strFrom As String
Dim strTo As String
Dim strCc As String
Dim strBcc As String
Dim strBody As String
strSubject = "Results from Excel Spreadsheet"
strFrom = "rdube02@gmail.com"
strTo = "rdube02@gmail.com"
strCc = ""
strBcc = ""
strBody = "The total results for this quarter are: " & Str(Sheet1.Cells(2, 1))

이 방식의 장점은 원하는 문자열을 자유롭게 조합하여 완성도 높은 이메일 본문을 만들고 strBody 변수에 할당할 수 있다는 점입니다.

& 연산자로 문자열을 연결하면 위 예제처럼 엑셀 시트의 데이터를 이메일 본문에 바로 삽입할 수 있습니다.

3단계: 외부 SMTP를 사용하도록 CDO 구성

다음 코드 섹션에서는 CDO가 외부 SMTP 서버를 통해 이메일을 전송하도록 구성합니다.

이 예제는 Gmail을 사용하는 비 SSL 설정입니다. CDO는 SSL도 지원하지만 이 글의 범위를 벗어나므로, SSL이 필요하다면 GitHub에서 공개된 고급 코드를 참고하시기 바랍니다.

Set CDO_Mail = CreateObject("CDO.Message")
On Error GoTo Error_Handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields
With SMTP_Config
 .Item("https://schemas.microsoft.com/cdo/configuration/sendusing") = 2
 .Item("https://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
 .Item("https://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
 .Item("https://schemas.microsoft.com/cdo/configuration/sendusername") = "email@website.com"
 .Item("https://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
 .Item("https://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
 .Item("https://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
 .Update
End With
With CDO_Mail
 Set .Configuration = CDO_Config
End With

4단계: CDO 설정 마무리

SMTP 서버 연결 구성이 끝났으므로, 이제 CDO_Mail 개체의 각 필드를 채우고 Send 명령을 실행하기만 하면 됩니다.

방법은 다음과 같습니다.

CDO_Mail.Subject = strSubject
CDO_Mail.From = strFrom
CDO_Mail.To = strTo
CDO_Mail.TextBody = strBody
CDO_Mail.CC = strCc
CDO_Mail.BCC = strBcc
CDO_Mail.Send
Error_Handling:
If Err.Description <> "" Then MsgBox Err.Description

Outlook 메일 개체를 사용할 때 종종 나타나는 팝업 창이나 보안 경고 메시지가 전혀 표시되지 않는다는 점이 큰 장점입니다.

CDO는 단순히 이메일을 조립한 뒤 SMTP 서버 연결 정보를 이용해 메시지를 전송할 뿐입니다. Microsoft Word나 Excel VBA 스크립트에 이메일 기능을 통합하는 가장 쉬운 방법입니다.

명령 단추를 이 스크립트에 연결하려면 코드 편집기에서 Sheet1을 클릭하여 해당 워크시트의 VBA 코드를 엽니다.

그리고 앞서 스크립트를 붙여넣은 함수의 이름을 입력합니다.

실제로 받은 편지함에 도착한 메시지는 다음과 같습니다.

참고: The transport failed to connect to the server(서버에 연결하지 못했습니다)라는 오류가 발생한다면, With SMTP_Config 아래 코드 줄에 입력한 사용자 이름, 비밀번호, SMTP 서버 주소, 포트 번호가 올바른지 확인하세요.

한 단계 더 나아가 전체 프로세스 자동화하기

버튼 클릭 한 번으로 엑셀에서 이메일을 보낼 수 있는 것만으로도 유용하지만, 이 기능을 정기적으로 사용해야 한다면 프로세스 전체를 자동화하는 것이 좋습니다.

이를 위해서는 매크로를 약간 수정해야 합니다. Visual Basic 편집기를 열고 앞서 작성한 코드 전체를 복사합니다.

다음으로 프로젝트(Project) 계층 구조에서 ThisWorkbook을 선택합니다.

코드 창 상단의 두 드롭다운 목록에서 각각 Workbook과 Open을 선택합니다.

그런 다음 이메일 스크립트를 Private Sub Workbook_Open() 안에 붙여넣습니다.

이렇게 하면 Excel 파일을 열 때마다 매크로가 자동으로 실행됩니다.

이제 작업 스케줄러(Task Scheduler)를 엽니다.

이 도구를 사용하면 Windows가 정해진 주기마다 스프레드시트를 자동으로 열도록 설정할 수 있고, 파일이 열리는 순간 매크로가 실행되어 이메일이 발송됩니다.

작업(Action) 메뉴에서 기본 작업 만들기(Create Basic Task...)를 선택한 뒤 마법사를 진행하여 작업(Action) 화면까지 이동합니다.

프로그램 시작(Start a program)을 선택하고 다음(Next)을 클릭합니다.

찾아보기(Browse) 버튼으로 컴퓨터에 설치된 Microsoft Excel의 위치를 찾거나, 경로를 복사하여 프로그램/스크립트(Program/script) 필드에 붙여넣습니다.

그다음 인수 추가(Add arguments) 필드에 Excel 문서의 전체 경로를 입력합니다.

마법사를 완료하면 일정 예약이 완료됩니다.

몇 분 뒤로 작업을 예약해 테스트를 진행해 보고, 정상 작동을 확인한 후 실제 사용할 주기로 수정하는 것을 권장합니다.

참고: 매크로가 제대로 실행되도록 보안 센터(Trust Center) 설정을 조정해야 할 수 있습니다.

이 경우 스프레드시트를 연 뒤 파일(File) > 옵션(Options) > 보안 센터(Trust Center)로 이동합니다.

여기서 보안 센터 설정(Trust Center Settings)을 클릭하고, 다음 화면에서 차단된 콘텐츠에 대한 정보 표시 안 함(Never show information about blocked content) 옵션을 선택합니다.

엑셀이 당신을 위해 일하도록 만들기

Microsoft Excel은 놀랍도록 강력한 도구이지만, 이를 최대한 활용하는 방법을 익히는 일은 다소 부담스럽게 느껴질 수 있습니다. 이 소프트웨어를 진정으로 마스터하려면 VBA에 능숙해져야 하는데, 이는 결코 쉬운 과정이 아닙니다.

하지만 그 결과는 노력할 가치가 충분합니다. VBA 경험이 조금만 쌓이면 엑셀이 기본적인 작업들을 자동으로 수행하게 만들 수 있어, 더 중요한 업무에 집중할 여유 시간이 생깁니다.

VBA 전문성을 갖추는 데는 시간이 걸리지만, 꾸준히 학습한다면 머지않아 그 결실을 확인하게 될 것입니다.

좋은 출발점은 Excel에서 VBA를 활용하는 방법을 다룬 상세 튜토리얼입니다. 이를 완주한 후라면 엑셀에서 이메일을 보내는 이 간단한 스크립트는 아주 쉽게 느껴질 것입니다.