[엑셀매크로] VBA활용하여 아웃룩 자동메일 보내기

안녕하세요~ 써머데이즈 입니다.

 

 

여러분 업무를 하면서 "아 맨날 똑같은 내용 메일 보내는거, 자동으로 됬음 좋겠다" 고민해보신 적 있으신가요? 저는 이러한 고민을 해결하고자 만들었던 VBA 매크로를 공유드리고자 합니다!

 

저같은 경우에는 1)SAP이라는 프로그램에서 문서를 생성하고, 2)사내 공용폴더에 저장 후, 3)메일 상에 해당 문서를 첨부하고 4) 각 담당자 CC 참조하여 이메일을 보내는 일을 했습니다. 

 

특수한 업무 환경 상 시중에 나온 소프트웨어를 사용해 자동화 할 수는 없었고, 제가 직접 VBA 코드를 짜야했던 것이죠. 이러한 결과물을 오늘 포스팅 해보고자 합니다.

 

1. Overview

​

사내 공유폴더에 저장된 파일을 메일에 첨부하여 업체에게 자동 전달

 

2. 사용방법

​

1) 엑셀 VBA(Visual Basic Application) 매크로를 사용하기 위해서는 먼저 Alt+F11 로 모듈 창을 켜주세요.

​

2) 그리고 도구- 참조 영역에 들어가서 Outlook을 추가해줍니다. (아웃룩으로 메일을 보내기 때문입니다.)

​

3) 스프레드 시트에 원하는 형태의 메일형태의 표를 만듭니다. 예를 들어서, 저희 회사는 첨부파일명은 "PO관리번호_업체명_개발용도 및 상세항목_제품이름"로 관리되고 있기 때문에, 이에 맞추어서 아래 항목도 자동으로 바뀌도록 설정 하였습니다. 그리고 제가 중요하게 봐야하는 부분만 하이라이트로 표시해두었습니다. 아래와 같은 변경을 위해 각각 수식을 걸어 놓았어요.

 

따라서 제가 자동화 한 것은

 

A) 업체명에 따라 받는 사람 주소 변경

B) 업체명에 따라 내부직원(담당자) 참조 변경

C) 개발용도에 따라 내부직원 참조 변경

D) 첨부파일 풀네임을 아웃룩 상 메일제목으로 설정

 

총 4가지 입니다. 이를 위해서 아래와 같은 다중함수를 사용하였습니다!

 

4) Sheet2 에는 backup 자료를 만듭니다. 업체명, 업체 담당자 이메일주소, 업체담당 내부직원 이메일주소를 적어놓고 아래 수식으로 끌어올겁니다. 개발용도(TR4)는 Sheet3에 입력을 해두고, 수식도 Sheet3로 바꿔주면 되겠죠!

 

 

 

5) 그런데 PLM이라고 되어있는 부분은 이렇게 끌어올 수가 없습니다. 그 이유는 업체에 따라 저희 팀 담당 직원과, 업체 담당자는 고정적으로 정해져 있지만 PLM(PR주체)부서는 절대변수가 아닌 상대변수이기 때문입니다. 그래서 일일히 넣어줘야하는데요.. 처음엔 PR 주체를 찾아서 하나하나 넣는것도 귀찮아서 드롭다운 목록을 만들어 놨습니다. 드롭박스 목록을 펼치면 수동으로 제가 선택할 수 있는거죠. 드롭박스는 데이터유효성검사-목록-범위드래그 해주면 쉽게 설정할 수 있습니다.

 

 

 

6) 추가로 복사폴더가 있는 이유는, 이메일을 보낼때 공유파일에 있는 첨부파일을 삭제하고 바로 보내는 것이 아니라 복사본을 보내는 것이기 때문에 제 개인 폴더에 복사본 저장위치를 설정하였습니다. 여기까지 마무리가 됬다면 가장 중요한 코드를 입력해주면 됩니다! 해당 매크로파일과 첨부파일(메일로 보낼)은 같은 폴더안에 위치해야합니다.

 

Go 버튼을 눌러주면, 파일을 복사하겠습니까? 메일을 전송하겠습니까? 라는 말이 나옵니다. 모두 엔터. 엔터.

---

 

아래와 같이 VBA 코드를 공유 합니다!

 

Option Explicit

Sub Copy_Icebox()

 

Dim s As String

Dim Current_Path As String

Dim File_Name As String

Dim Star As String

Dim Desti As String

 

Current_Path = ThisWorkbook.Path

File_Name = Cells(3, 4).Value & ".pdf"

 

Star = ThisWorkbook.Path & "\" & File_Name

Desti = Cells(9, 4).Value & "\" & File_Name

 

FileCopy Star, Desti

 

MsgBox "파일 복사 완료" & Chr(10) & Desti

 

 

If MsgBox("메일을 전달하겠습니까?", vbQuestion + vbYesNo, "자동 발송") = vbYes Then

 

Dim olApp As Outlook.Application

Dim olMail As MailItem

Dim sAttFile As String

 

Set olApp = New Outlook.Application

Set olMail = olApp.CreateItem(olMailItem)

 

With olMail

 

s = Cells(8, 4).Value & ";" & Cells(10, 4).Value & ";" & Cells(11, 4).Value

s = Replace(s, ";", ";")

If Right(s, 1) = ";" Then s = Left(s, Len(s) - 1)

If Left(s, 1) = ";" Then s = Right(s, Len(s) - 1)

 

.To = Cells(5, 4).Value

.CC = s

.Subject = Cells(6, 4).Value

 

.Attachments.Add Star

.Body = Cells(7, 4).Value

.Send

 

End With

Set olMail = Nothing

Set olApp = Nothing

 

MsgBox ("메일 발송 완료")

 

Exit Sub

 

ErrorMessage:

MsgBox ("파일 이름 오류 - 다시 시도하세요")

End If

End

 

---

파일명 입력 후
상대변수만 드롭다운으로 선택
go 버튼만 눌러주면..

자동이메일 완성.

업무시간 20분에서 30초로 단축완료!