Microsoft Excel에서 이메일을 보내려면 몇 가지 간단한 스크립트만 있으면 됩니다. 스프레드시트에 이 기능을 추가하면 Excel에서 훨씬 더 많은 작업을 수행할 수 있습니다.
Excel 매크로는 프로그래밍 지식이 많지 않아도 VBA 스크립트와 동일한 많은 작업을 수행할 수 있습니다. VBA를 사용하면 모든 PC 정보가 포함된 스프레드시트 보고서 작성과 같은 고급 루틴을 구현할 수 있습니다.
이 튜토리얼을 동영상으로 보고 싶으신가요? 저희가 준비했습니다!
Excel에서 이메일을 보내는 이유는 무엇인가요?
Microsoft Excel에서 이메일을 보내야 하는 이유는 여러 가지가 있습니다.
매주 문서나 스프레드시트를 업데이트하는 직원이 있는데, 업데이트가 있을 때 이메일 알림을 받고 싶을 수도 있습니다. 또는 연락처 스프레드시트가 있는데 모든 연락처에 한 번에 하나의 이메일을 보내고 싶을 수도 있습니다.
Excel에서 이메일 브로드캐스트 스크립트를 작성하는 것이 복잡할 것이라고 생각할 수 있습니다. 전혀 그렇지 않습니다. 이 글에서 소개하는 기법은 Excel VBA에서 오랫동안 사용되어 온 기능인 협업 데이터 개체(CDO)를 활용합니다.
CDO는 아주 초기 세대의 OS부터 Windows에서 사용된 메시징 구성 요소입니다. 예전에는 CDONTS라고 불렸지만, Windows 2000 및 XP가 등장하면서 “Windows 2000용 CDO”로 대체되었습니다. 이 구성 요소는 Microsoft Word 또는 Excel 내 VBA 설치에 이미 포함되어 있으며 바로 사용할 수 있습니다.
이 구성 요소를 사용하면 VBA를 사용하여 Windows 제품 내에서 이메일을 매우 쉽게 보낼 수 있습니다. 이 예에서는 Excel의 CDO 구성 요소를 사용하여 특정 Excel 셀의 결과를 전달하는 이메일을 보내겠습니다.
1단계: Gmail 계정 준비
Microsoft Excel에서 이메일을 보내기 위해 Gmail을 사용하지만, 아래 매크로를 사용자 지정하여 다른 이메일 클라이언트와 함께 사용할 수 있습니다. Gmail은 더 이상 타사 앱 액세스를 허용하지 않으므로 먼저 Gmail의 2단계 인증을 사용하도록 설정해야 한다는 점에 유의하세요.
Google 계정의 보안 페이지 에서 Google에 로그인하기 아래 앱 비밀번호를 클릭합니다. 앱 비밀번호 화면에서 앱 선택 드롭다운 메뉴를 찾아 메일을 선택합니다. 장치 선택에서 Windows 컴퓨터를 선택합니다. 그런 다음 생성을 클릭합니다.
매크로를 구성할 때 필요한 16자 앱 비밀번호를 적어 둡니다.
앱 비밀번호 옵션이 표시되지 않는 경우 계정에 2단계 인증을 설정했지만 학교나 직장과 같은 제3자가 계정을 관리하거나 Google의 고급 보호 프로그램 에 등록했기 때문일 수 있습니다.
2단계: VBA 매크로 만들기
팁: 시작하기 전에 작업할 Excel 파일을 매크로 사용 가능 통합 문서, 즉 XLSM 형식으로 저장하세요.
먼저 Excel 개발자 탭이 필요합니다. 탭이 보이지 않는 경우 활성화하는 방법은 다음과 같습니다:
⭐ 파일 > 옵션으로 이동합니다.
⭐ 리본 사용자 지정 및 기본 탭에서 개발자 옵션을 선택합니다.
⭐ 확인을 클릭하여 변경 사항을 저장합니다.
Excel의 개발자 탭에서 컨트롤 상자에서 삽입을 클릭한 다음 명령 단추를 선택합니다.
시트에 그린 다음 개발자 리본에서 매크로를 클릭하여 새 매크로를 만듭니다.
만들기 버튼을 클릭하면 VBA 편집기가 열립니다.
편집기에서 도구 > 참조로 이동하여 CDO 라이브러리에 참조를 추가합니다.
목록을 아래로 스크롤하여 Windows 2000용 Microsoft CDO 라이브러리를 찾습니다. 확인란을 선택하고 확인을 클릭합니다.
확인을 클릭할 때 스크립트를 붙여넣을 함수 이름을 메모해 두세요. 나중에 필요할 것입니다.
3단계: 매크로 구성
이제 메일 개체를 만들고 이메일 전송에 필요한 모든 필드를 설정할 준비가 되었습니다. 대부분의 필드는 선택 사항이지만 받는 사람 및 보내는 사람 필드는 필수라는 점에 유의하세요. 아래의 모든 코드 스니펫을 Module1(코드) 창에 붙여넣습니다.
전체 코드는 다음과 같습니다:
Sub Send_Emails()
Dim NewMail As CDO.Message
Dim mailConfig As CDO.Configuration
Dim fields As Variant
Dim msConfigURL As String
On Error GoTo Err:
'early binding
Set NewMail = New CDO.Message
Set mailConfig = New CDO.Configuration
'load all default configurations
mailConfig.Load -1
Set fields = mailConfig.fields
'Set All Email Properties
With NewMail
.From = "[email protected]"
.To = "[email protected]"
.CC = ""
.BCC = ""
.Subject = "Send Email From an Excel Spreadsheet"
.TextBody = "This is the body of your email. And here is some added data:" & Str(Sheet1.Cells(2, 1))
.Addattachment "c:\data\email.xlsx" 'Optional file attachment; remove if not needed.
.Addattachment "c:\data\email.pdf" 'Duplicate the line for a second attachment.
End With
msConfigURL = "http://schemas.microsoft.com/cdo/configuration"
With fields
.Item(msConfigURL & "/smtpusessl") = True 'Enable SSL Authentication
.Item(msConfigURL & "/smtpauthenticate") = 1 'SMTP authentication Enabled
.Item(msConfigURL & "/smtpserver") = "smtp.gmail.com" 'Set the SMTP server details
.Item(msConfigURL & "/smtpserverport") = 465 'Set the SMTP port Details
.Item(msConfigURL & "/sendusing") = 2 'Send using default setting
.Item(msConfigURL & "/sendusername") = "[email protected]" 'Your gmail address
.Item(msConfigURL & "/sendpassword") = "password" 'Your password or App Password
.Update 'Update the configuration fields
End With
NewMail.Configuration = mailConfig
NewMail.Send
MsgBox "Your email has been sent", vbInformation
Exit_Err:
'Release object memory
Set NewMail = Nothing
Set mailConfig = Nothing
End
Err:
Select Case Err.Number
Case -973 'Could be because of Internet Connection
MsgBox "Check your internet connection." & vbNewLine & Err.Number & ": " & Err.Description
Case -975 'Incorrect credentials User ID or password
MsgBox "Check your login credentials and try again." & vbNewLine & Err.Number & ": " & Err.Description
Case Else 'Report other errors
MsgBox "Error encountered while sending email." & vbNewLine & Err.Number & ": " & Err.Description
End Select
Resume Exit_Err
End Sub
다음은 사용자 지정해야 하는 섹션 및 필드입니다:
⭐ NewMail 사용: 이 섹션에는 이메일 본문을 포함하여 이메일 전송을 위한 모든 매개변수가 포함되어 있습니다. 보낸 사람 필드에는 Gmail 주소가 포함되어야 하지만 다른 필드는 원하는 대로 자유롭게 설정할 수 있습니다. 본문의 경우 위와 같이 & 문자열을 사용하여 Microsoft Excel 시트의 데이터를 이메일 메시지에 바로 삽입하여 메시지의 구성 요소를 조합할 수 있습니다. 하나 이상의 파일을 첨부할 수도 있습니다.
⭐ 필드 포함: Gmail 계정에 대한 SMTP 설정을 구성하는 곳입니다. 코드를 복사할 때 smtpserver 및 smtpserverport 필드는 그대로 둡니다. 각 필드에 Gmail 사용자 아이디와 16자리 앱 비밀번호를 입력합니다.
4단계: 매크로 테스트
VBA 편집기에서 > 하위/사용자 양식 실행으로 이동하거나 F5 키를 눌러 매크로를 테스트합니다. 이메일 전송에 실패하면 오류 메시지가 표시됩니다. 그렇지 않으면 이메일이 성공적으로 전송되었다는 확인 메시지가 표시됩니다.
전송이 서버에 연결하지 못했습니다라는 오류가 표시되면 With 필드 아래에 나열된 코드 줄에 올바른 사용자 아이디, 비밀번호, SMTP 서버 및 포트 번호를 입력했는지 확인하세요.
5단계: 명령 버튼을 스크립트에 연결
명령 버튼을 이 스크립트에 연결하려면 코드 편집기로 이동하여 Sheet1을 두 번 클릭하여 해당 워크시트에 대한 VBA 코드를 확인합니다. 왼쪽의 드롭다운에서 버튼(예: CommandButton1)을 선택하고 오른쪽에서 동작을 정의한 다음 작동을 클릭합니다. 그런 다음 위의 스크립트를 붙여넣은 함수의 이름을 입력합니다(이 예에서는 Send_Emails).
이제 시트로 돌아가서 버튼을 클릭하여 이메일을 보냅니다.
다음은 받은 편지함에서 메시지가 어떻게 표시되는지 보여주는 예입니다:
한 걸음 더 나아가 전체 프로세스 자동화
Excel에서 버튼 하나만 누르면 이메일을 보낼 수 있다는 것은 좋은 일입니다. 그러나 이 기능을 정기적으로 사용하고 싶을 수도 있으며, 이 경우 프로세스를 자동화하는 것이 좋습니다. 그렇게 하려면 매크로를 변경해야 합니다. Visual Basic 편집기로 이동하여 지금까지 작성한 코드 전체를 복사하여 붙여넣습니다.
그런 다음 VBAProject 계층 구조에서 ThisWorkbook을 두 번 클릭합니다.
코드 창 상단에 있는 두 드롭다운 필드에서 통합 문서를 선택하고 메서드 드롭다운에서 열기를 선택합니다.
위의 이메일 스크립트를 Sub Workbook_Open()에 붙여 넣습니다.
이렇게 하면 Excel 파일을 열 때마다 매크로가 실행됩니다.
다음으로 작업 스케줄러를 엽니다. 이 도구를 사용하여 Windows에 일정 간격으로 스프레드시트를 자동으로 열도록 요청하면 매크로가 실행되어 이메일을 전송합니다.
작업 메뉴에서 기본 작업 만들기…를 선택하고 마법사를 통해 작업 화면이 나올 때까지 진행합니다.
프로그램 시작을 선택하고 다음을 클릭합니다. 찾아보기 버튼을 사용하여 컴퓨터에서 Microsoft Excel의 위치를 찾거나 경로를 복사하여 프로그램/스크립트 필드에 붙여넣습니다. 그런 다음 인수 추가 필드에 Microsoft Excel 문서의 경로를 입력합니다. 마법사를 완료하면 스케줄링이 완료됩니다.
향후 몇 분 동안 작업을 예약하여 테스트를 실행한 다음 작업이 작동하는 것을 확인한 후 작업을 수정하는 것이 좋습니다.
참고: 매크로가 제대로 실행되도록 하기 위해 신뢰 센터 설정을 조정해야 할 수도 있습니다.
이렇게 하려면 스프레드시트를 열고 파일; 옵션; 신뢰 센터로 이동합니다. 여기에서 신뢰 센터 설정…을 클릭하고 다음 화면에서 라디오 다이얼을 차단된 콘텐츠에 대한 정보 표시 안 함으로 설정합니다.
Microsoft Excel로 업무 효율 높이기
Microsoft Excel은 매우 강력한 도구이지만, 이를 최대한 활용하는 방법을 배우는 것은 다소 어려울 수 있습니다. 소프트웨어를 진정으로 마스터하려면 VBA에 익숙해져야 하는데, 이는 결코 쉬운 일이 아닙니다.
그러나 결과는 그 자체로 말해줍니다. 약간의 VBA 경험만 있으면 곧 Microsoft Excel에서 기본 작업을 자동으로 수행하도록 설정할 수 있으므로 더 긴급한 문제에 더 많은 시간을 집중할 수 있습니다.