[vba] CDO 클래스로 SMTP 메일 보내기 - 네이버 SMTP 오류와 SSL 설정

엑셀에서 보고서를 만들고 나서 그 파일을 바로 메일로 보내야 하는 일이 종종 있습니다. 아웃룩이 깔려 있으면 Outlook 개체를 쓰면 되지만, 아웃룩이 없는 PC라면 윈도우에 기본으로 들어 있는 CDO(Collaboration Data Objects)로 SMTP 서버에 직접 붙어서 보내는 방법이 제일 무난합니다.

아래 함수는 네이버 엑셀 VBA 카페에 공개된 코드로, 수신자·발신자·제목·본문·첨부파일 경로를 받아 메일을 보내고 성공하면 True를 돌려줍니다. 문제는 이 코드로 네이버 SMTP를 쓰다가 어느 날부터 .send에서 오류가 난다는 질문이 올라왔다는 것이고, 데브피아에 달린 답변이 그 해결 방향을 보여 줍니다. 두 코드를 같이 보면 원인이 보입니다.

첨부파일까지 보내는 CDOSendMail 함수

CDO.Configuration의 Fields에 서버 주소, 보내는 방식, 인증 방식, 계정 정보를 넣고 .Update로 확정한 다음, CDO.Message에 그 설정을 연결해서 보내는 구조입니다. 설정 키가 전부 http://schemas.microsoft.com/cdo/configuration/... 형태의 긴 문자열이라 상수로 빼 두었습니다. 계정과 비밀번호는 자리표시로 바꿔 두었으니 본인 것으로 채우세요.

Option Explicit
Private Const cdoSendUsingMethod = "http://schemas.microsoft.com/cdo/configuration/sendusing"
Private Const cdoSendUsingPort = 2
Private Const cdoSMTPServer = "http://schemas.microsoft.com/cdo/configuration/smtpserver"
Private Const cdoSMTPServerPort = "http://schemas.microsoft.com/cdo/configuration/smtpserverport"
Private Const cdoSMTPConnectionTimeout = "http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"
Private Const cdoSMTPAccountName = "http://schemas.microsoft.com/cdo/configuration/smtpaccountname"
Private Const cdoSMTPAuthenticate = "http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"
Private Const cdoBasic = 1
Private Const cdoSendUserName = "http://schemas.microsoft.com/cdo/configuration/sendusername"
Private Const cdoSendPassword = "http://schemas.microsoft.com/cdo/configuration/sendpassword"
Private Const strSMTP As String = "smtp.naver.com"
Private Const strID As String = "*******"
Private Const strPW As String = "********"

'## 실행테스트...
Sub Test()
    If CDOSendMail("RECEIVER@example.com", "SENDER@example.com", "테스트", "ㅎㅎㅎ", "C:\aaa.txt") Then
        MsgBox "메세지 전송 성공"
    End If
End Sub

'========================================================================================================
' Procedure : CDOSendMail
' DateTime  : 2007-10-05 14:19
' Author    : 이석재 (http://cafe.naver.com/xlsvba/380)
' Param     : mailto - 수신자(s)
'             mailfrom - 송신자(s)
'             mailsubject- 제목(s)
'             mailbody - 내용(s)
'             mailattatch - 첨부파일(s)
'=========================================================================================================
Private Function CDOSendMail(mailto As String, _
                       mailfrom As String, _
                       mailsubject As String, _
                       mailbody As String, _
                       mailattatch As String) As Boolean

  Dim oMsg   As Object
  Dim oConf  As Object

    On Error GoTo CDOSendMail_Error
    Set oMsg = CreateObject("CDO.Message")              '## CDO.Message 개체 생성
    Set oConf = CreateObject("CDO.Configuration")       '## CDO.Configuration 개체 생성

    With oConf.Fields

        oConf.Load -1

        .Refresh                                        '## Fields 옵션 초기화
        .Item(cdoSendUsingMethod) = 2                   '## 보내는 방식 (내부 픽업-1, 외부 SMTP 포트-2)
        .Item(cdoSMTPServer) = strSMTP                  '## SMTP 서버 주소
        .Item(cdoSMTPConnectionTimeout) = 10            '## 연결 대기시간 제한(초)
        .Item(cdoSMTPAuthenticate) = cdoBasic           '## 인증 방식 (인증이 필요한 서버에 한해)
        .Item(cdoSendUserName) = strID                  '## 계정 ID
        .Item(cdoSendPassword) = strPW                  '## 계정 PW
        '.Item(cdoURLGetLatestVersion) = True
        .Update                                         '## 설정 반영
    End With
    With oMsg
        Set .Configuration = oConf

        If InStr(1, mailto, "@", vbTextCompare) Then
            .To = mailto                                    '## 수신주소
            .BodyPart.Charset = "ks_c_5601-1987"            '## 한글설정
            .From = mailfrom                                '## 송신주소
            .Subject = mailsubject                          '## 제목
            .TextBody = mailbody                            '## 내용
            .AutoGenerateTextBody = False                   '## 바디설정:텍스트
            .AddAttachment mailattatch                      '## 첨부파일
'            .HTMLBodyPart.Charset = "ks_c_5601-1987"        '## 바디설정:HTML
            .send                                           '## 보내기
            CDOSendMail = True
        End If
    End With

CDOSendMail_Error:
    Call Err_Message("CDOSendMail", "modDGR")
    If Not oMsg Is Nothing Then
        Set oMsg = Nothing                                  '## 메모리제거
    End If

    If Not oMsg Is Nothing Then
        Set oConf = Nothing                                 '## 메모리제거
    End If
End Function

'================================================================================
' Procedure   : Err_Message
' DateTime    : 2007-06-26 17:05
' Author      : 서은아빠 (http://cafe.naver.com/xlsvba)
' Purpose     : 오류메세지 처리(모듈위치, 프로시저(함수)명 리턴
' Param       : strFuncName - 해당 함수나 프로시저명
'             : strModuleName - 프로시저를 포함하고 있는 모듈명
'================================================================================
Private Sub Err_Message(ByVal strFuncName As String, ByVal strModuleName As String)

  If Err.Number <> 0 Then
      MsgBox "오류가 발생하였습니다." & vbCrLf & _
             "오류의 내용은 " & Err.Description & vbCrLf & _
             "오류의 위치는 Function(Procedure) : " & strFuncName & "Module : " & strModuleName, vbCritical
  End If

  On Error GoTo 0

End Sub

CDOSendMail_Error: 레이블 앞에 Exit Function이 없어서 정상 전송 후에도 Err_Message가 호출되지만, 그 안에서 Err.Number를 확인하니 오류가 없으면 아무 메시지도 뜨지 않습니다. 의도한 구조로 보면 됩니다. 다만 맨 아래 두 번째 If는 oMsg를 다시 검사하는데, 바로 위에서 oMsg를 이미 Nothing으로 만들었기 때문에 oConf는 정리되지 않습니다. If Not oConf Is Nothing Then으로 고쳐 쓰는 게 맞습니다. 함수가 끝나면 지역 변수라 어차피 해제되긴 하지만 보기에 헷갈리니까요.

잘 되다가 갑자기 전송 오류가 나는 이유

위 코드를 다시 보면 SMTP 포트와 SSL 설정이 아예 없습니다. 기본 포트(25)로 암호화 없이 접속하는 셈인데, 네이버를 비롯한 메일 서비스들이 보안 접속만 받도록 바뀌면 이런 코드는 .send에서 바로 막힙니다. 코드가 어제와 똑같은데 오늘 안 된다면 대개 서버 쪽 정책이 바뀐 경우입니다.

데브피아 답변으로 올라온 코드는 이 부분을 채워 넣었습니다. cdoSMTPServerPort로 포트를 지정하고 cdoSMTPUseSSL을 True로 켜는 게 차이입니다. 이 코드는 CDO.Message를 직접 타입으로 선언하는 초기 바인딩 방식이라, VBE의 도구 > 참조에서 Microsoft CDO for Windows 2000 Library를 체크해야 상수 이름(cdoDefaults, cdoSMTPUseSSL 등)이 인식됩니다.

    Dim iMsg As CDO.Message
    Dim iConf As CDO.Configuration
    Dim strbody As String
    Set iMsg = New CDO.Message
    Set iConf = New CDO.Configuration
        iConf.Load cdoDefaults     ' CDO Source Defaults
        With iConf.Fields
            .Item(cdoSMTPServer) = "" ' SMTP 서버
            .Item(cdoSendUsingMethod) = cdoSendUsingPort ' 보내는 방법 선택 (포트, 픽업)
            .Item(cdoSMTPServerPort) = ' SMTP 서버 포트
            .Item(cdoSMTPUseSSL) = True ' SMTP 서버에서 SSL 사용 유무
            .Item(cdoSendUserName) = "" ' 계정 ID
            .Item(cdoSendPassword) = "" ' 계정 암호
            .Item(cdoSMTPAuthenticate) = 1
            .Update
        End With
    strbody = "Hi there"
    With iMsg
        Set .Configuration = iConf ' SMTP 서버를 설정한 개체를 할당한다.
        .To = "" ' 메일을 받을 사람의 메일 주소
        .From = "" ' 보내는 사람 메일 주소, 즉 사용하는 SMTP에 있는 메일 계정
        .Subject = "New figures" ' 메일 제목
        .TextBody = strbody ' 메일 내용
        .Send ' 메일을 보낸다.
    End With
    Set iConf = Nothing
    Set iMsg = Nothing

답변 코드는 틀만 보여 준 것이라 .Item(cdoSMTPServerPort) = 뒤에 값이 비어 있습니다. 이대로는 컴파일 오류가 나니 숫자를 꼭 넣어야 합니다. 네이버 SMTP라면 SSL 포트 465를 넣고, 앞의 CDOSendMail 함수에도 같은 방식으로 아래 두 줄을 .Update 전에 추가하면 됩니다. 늦은 바인딩이라 상수가 없으니 키 문자열을 직접 씁니다.

        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

그래도 안 될 때 확인할 것

네이버 메일은 웹 메일 환경설정에서 POP3/SMTP 사용을 허용해 두지 않으면 외부 프로그램의 SMTP 접속을 받지 않습니다. 계정에 2단계 인증을 켜 두었다면 로그인 비밀번호 대신 애플리케이션 비밀번호를 써야 하고요. 그리고 From 주소는 로그인한 계정의 주소와 같아야 합니다. 다른 주소를 넣으면 서버가 발신자 위조로 보고 거부하는 경우가 많습니다. 한글 제목이 깨진다면 .BodyPart.Charset 값을 "utf-8"로 바꿔 보는 것도 방법입니다.

참고: CDO 메일보내기 (네이버 엑셀 VBA 카페) | 데브피아 Q&A