엑셀에서 자동으로 메일 보내기 2 - VBA 코드 진행 [엑셀 VBA]

https://data-so-hard.tistory.com/113

 

지난 글에서 메일 자동화를 하기 위한 메일 세팅과 엑셀 시트를 구성했습니다. 이번 편에서는 본격적으로 VBA 코드를 작성해보고 실행해보겠습니다. 

 

우선 코드를 통해 구현해보려는 조건에 대해 작성하겠습니다.

 

1. 매월 직원들에게 보내는 급여 명세서 메일을 자동화 하겠다.

2. 엑셀 시트에 직원 이름/ 메일주소 / 급여 월 테이블을 구성해 3개의 컬럼 값을 통해 자동화를 진행하겠다.

3. 엑셀 시트에 있는 정보를 기반으로 "직원이름_급여 명세서.pdf"가 있는 파일 경로를 설정해

메일과 함께 첨부파일을 함께 보낼 것이다. 

4. 엑셀 시트 "메일 전송" 버튼을 만들어서 클릭하면 메일이 발송되도록 만들 것이다. 

 

함수를 만들고 변수들을 선언해보겠습니다. 

Sub SendGmailWithMonthlyAttachment() -- 함수 선언

Dim MailObj As Object
Dim CDOConfig As Object
Dim SMTPConfig As Object
Dim recipient As String
Dim subject As String
Dim body As String
Dim attachmentPath As String
Dim employeeName As String
Dim monthFolder As String
Dim lastRow As Long
Dim ws As Worksheet
Dim i As Long
Dim baseFolderPath As String

 

1. 변수 선언: 이메일 전송에 필요한 변수들과 엑셀 데이터 관련 변수를 선언합니다.

 

MailObj : 이 변수는 메일 메시지를 보내는 데 사용되는 CDO.Message 객체를 저장합니다. 이 객체를 통해 메일의 수신자, 제목, 본문, 첨부파일 등을 설정할 수 있습니다. 객체(Object)는 VBA에서 어떤 특수한 기능을 수행하는데 필요한 속성 및 메서드를 가진 데이터입니다. 예를 들어, MailObj는 이메일을 구성하고 전송하는 역할을 합니다.

 

CDOConfig : 이 변수는 CDO.Configuration 객체를 저장하며, 메일 전송을 위한 SMTP 서버 설정을 정의하는 데 사용됩니다. 여기에는 SMTP 서버 주소, 포트 번호, SSL 설정 등이 포함됩니다.

 

SMTPConfig : 이 변수는 CDO.Configuration.Fields 객체를 저장합니다. 이 필드들을 통해 SMTP 서버에 대한 자세한 설정(예: 서버 주소, 인증 방식 등)을 구성합니다.recipient: 이메일 수신자의 주소

 

subject: 이메일 제목
body: 이메일 본문
attachmentPath: 첨부파일 경로
employeeName: 사원 이름
monthFolder: 월 구분 값 (예: 9월)
lastRow: 마지막 데이터가 있는 행 번호
ws: 엑셀 시트 객체

 

변수를 지정했으니 본격적으로 코드를 진행시켜보겠습니다. 

' 엑셀 시트 지정 (예: Sheet1)
    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' 마지막 행 찾기(이메일 목록의 끝)
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
    
    ' 첨부파일이 있는 기본 폴더 경로 지정(예: C:\Salaries\)
    baseFolderPath = "C:\Salaries\"  ' 기본 폴더 경로를 원하는 위치로 변경
    
    ' 각 행에 대해 이메일 전송
    For i = 2 To lastRow ' 헤더가 있다고 가정하고 2번째 행부터 시작
        recipient = ws.Cells(i, 2).Value ' B열에 이메일 주소가 있다고 가정
        employeeName = ws.Cells(i, 1).Value ' A열에 직원 이름이 있다고 가정
        monthFolder = ws.Cells(i, 3).Value ' C열에 급여 월 구분(예: 9월)이 있다고 가정
        subject = employeeName & "님의" & monthFolder & " 급여 명세서를 첨부해드립니다."
        body = "안녕하세요, " & employeeName & "님. " & monthFolder & " 급여 명세서를 첨부해드립니다."
        
        ' 사원 이름과 월에 맞는 첨부파일 경로 지정 (예: C:\Salaries\9월\홍길동_급여명세서.pdf)
        attachmentPath = baseFolderPath & monthFolder & "\" & employeeName & "_급여명세서.pdf"
        
        ' 파일 존재여부 확인
        If Dir(attachmentPath) <> "" Then ' 파일이 존재하면
            ' CDO Configuration 객채 생성
            Set MailObj = CreateObject("CDO.Message")
            Set CDOConfig = CreateObject("CDO.Configuration")
            CDOConfig.Load -1
            Set SMTPConfig = CDOConfig.Fields
            
            ' Gmail SMTP 서버 설정
            With SMTPConfig
                .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
                .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
                .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
                .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
                .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
                .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "youremail@youremail.com" ' Gmail 주소
                .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "appPassword" ' 앱 비밀번호
                .Update
            End With
            
            ' 이메일 설정
            With MailObj
                Set .Configuration = CDOConfig
                .To = recipient
                .From = "your_email@gmail.com" ' 보내는 사람 이메일 주소
                .subject = subject
                .TextBody = body
                
                ' 첨부파일 추가 (사원별 파일)
                .AddAttachment attachmentPath
                
                .Send
            End With
        Else
            ' 파일이 없는 경우 경고 메시지를 출력
            MsgBox employeeName & "님의 " & monthFolder & " 급여 명세서 파일을 찾을 수 없습니다: " & attachmentPath, vbExclamation
        End If
    Next i
    
    ' 객체 해제
    Set MailObj = Nothing
    Set CDOConfig = Nothing
End Sub

 

2. 마지막 행 찾기

lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

이 코드는 A열에서 마지막으로 데이터가 입력된 셀의 행 번호를 찾습니다. 이를 통해 데이터를 어디까지 처리해야 하는지 결정합니다. 예를 들어, 10개의 사원 정보가 있다면 lastRow는 10이 됩니다.

 

3. 기본 폴더 경로 설정

baseFolderPath = "C:\Salaries\"

baseFolderPath는 첨부파일이 저장된 기본 폴더 경로입니다. 이 경로는 급여 명세서가 저장된 폴더로 설정됩니다.
각 사원의 급여 명세서가 해당 월 폴더에 저장된다고 가정합니다. 예: C:\Salaries\9월\홍길동_급여명세서.pdf

 

4. 각 행에 대해 메일 전송 (루프)

For i = 2 To lastRow ' 2번째 행부터 시작 (헤더 제외)

첫 번째 행은 헤더라고 가정하고, 2번째 행부터 마지막 행까지 루프를 돌며 메일을 전송합니다.
이 루프는 각 사원의 정보를 읽고 메일을 보내는 작업을 반복합니다.

 

5. 메일 내용 및 첨부파일 경로 설정

recipient = ws.Cells(i, 2).Value ' B열: 이메일 주소
employeeName = ws.Cells(i, 1).Value ' A열: 사원 이름
monthFolder = ws.Cells(i, 3).Value ' C열: 월 구분 (예: "9월")
subject = employeeName & "님의 " & monthFolder & " 급여 명세서"
body = "안녕하세요, " & employeeName & "님. " & monthFolder & " 급여 명세서를 첨부해드립니다."

' 첨부파일 경로 설정
attachmentPath = baseFolderPath & monthFolder & "\" & employeeName & "_급여명세서.pdf"

recipient, employeeName, monthFolder: 각각의 행에서 이메일 주소, 사원 이름, 월 구분을 가져옵니다.
subject, body: 이메일 제목과 본문을 설정합니다. 예를 들어, "홍길동님의 9월 급여 명세서"와 같은 제목을 자동으로 생성합니다.
attachmentPath: 사원 이름과 월을 조합하여 첨부파일의 경로를 동적으로 설정합니다. 예: C:\Salaries\9월\홍길동_급여명세서.pdf

 

6. 파일 존재 여부 확인

If Dir(attachmentPath) <> "" Then

Dir 함수를 사용하여 지정된 경로에 파일이 존재하는지 확인합니다. 파일이 존재하면 이메일에 첨부하고, 파일이 없으면 경고 메시지를 출력합니다.

 

7. 메일 전송 설정 및 발송

Set MailObj = CreateObject("CDO.Message")
Set CDOConfig = CreateObject("CDO.Configuration")
CDOConfig.Load -1
Set SMTPConfig = CDOConfig.Fields

With SMTPConfig
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "your_email@gmail.com"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "your_app_password"
    .Update
End With

With MailObj
    Set .Configuration = CDOConfig
    .To = recipient
    .From = "your_email@gmail.com"
    .Subject = subject
    .TextBody = body
    
    ' 첨부파일 추가
    .AddAttachment attachmentPath
    
    .Send
End With

MailObj와 CDOConfig 객체는 Gmail의 SMTP 서버를 통해 메일을 보내는 데 필요한 설정을 저장합니다.
SMTP 설정: Gmail SMTP 서버(smtp.gmail.com)를 사용하여 SSL을 통해 포트 465로 이메일을 보냅니다. 사용자의 Gmail 계정과 앱 비밀번호가 필요합니다.
메일 구성: recipient, subject, body, AddAttachment로 설정된 내용을 기반으로 메일을 구성하고 Send 메서드로 전송합니다.

8. 메모리 해제

Set MailObj = Nothing
Set CDOConfig = Nothing

메일 객체와 설정 객체를 해제하여 메모리 누수를 방지합니다.