VBA ExcelからGmailでメールを送信する

このチュートリアルでは、VBAを使用してExcelからGmailでメールを送信する方法を紹介します。

Microsoft CDOリファレンスを追加する

ExcelからGmailでメールを送信するためには、まず、CDO参照ライブラリをExcelプロジェクトに追加する必要があります。 VBAエディタで、[ツール]>[リファレンス]をクリックします。

Microsoft CDO Referenceを検索し、OKをクリックします。

GMailで電子メールを送信する

ExcelからGmailでメールを送るには、まずCDOオブジェクトを宣言する必要があります。（Dim gMail as CDO.Message）

次に、メッセージのSSL認証を設定します。 つまり、メールを正しく送信するために、SMTPサーバーとポートの詳細、ユーザー名とパスワードを設定する必要があります。

以下のコードでは、Excel関数の冒頭でこのような設定が必要です。 メールの受信者(strTo)と件名(strSubject)は必要ですが、メールのCCと本文はオプションの変数にすることができます。

Function CreateEmail(strTo As String, strSubject As String, Optional strCC As String, Optional strBody As String)

'CDOオブジェクトを作成する 
   Dim gMail As CDO.Message 
   Set gMail = New CDO.Message

'SSL認証を有効にする 
   gMail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

'SMTP認証の有効化=trueにする (1) 
   gMail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

'SMTPサーバーとポートの設定 詳細 
   gMail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
   gMail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
   gMail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 

'自分のgmailアカウントのユーザー名とパスワードをここに設定する
    gMail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "johnsmith@gmail.com" 
   gMail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "*****" 

'設定フィールドを更新する 
   gMail.Configuration.Fields.Update

これができれば、メールを作成し、関数を完成させることができます。

'メールのプロパティと送信するファイルを設定する 
   With gMail 
      .Subject = strSubject
      .From = "stitchmel@gmail.com" 
      .To = strTo 
      .TextBody = strBody 
   End With
'メール送信 
   gMail.Send
End Function

次に、次のコードで関数を呼び出すことができます。

Sub SendEmail
'メールの本文を作成
Dim strText as string
   strText = "おはようございます。  お元気ですか - これはテストメールです"
'関数の引数を入力する - CCは空白のままなので、プレースホルダとしてカンマを置く
   Call CreateEmail("jim.smith@gmail.com", "テストメール", , strText)
End Sub

GMailでワークブックを送信する

GMail でワークブックを添付して送信するには、上記の関数とほぼ同じものを作成し、いくつかの追加を行います。 ワークブックを選択するためのコードを追加し、選択したファイルをメールに添付する必要があります。 ここでは、Microsoft OfficeFileDialogコマンドを使用して送信するファイルを選択し、次に電子メールのAddAttachmentプロパティを使用してファイルを添付ファイルとして追加します。

Function SendWorkbook(strTo As String, strSubject As String, Optional strCC As String, Optional strBody As String) As Boolean
On Error Goto eh:

'CDOオブジェクトを作成する
   Dim gMail As CDO.Message
   Set gMail = New CDO.Message

'SSL認証を有効にする
   gMail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True

'SMTP認証の有効化=trueにする (1)
   gMail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1

'SMTPサーバーとポートの設定 詳細
   gMail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
   gMail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
   gMail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

'自分のgmailアカウントのユーザー名とパスワードをここに設定する
   gMail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendusername") = "johnsmith@gmail.com"  
   gMail.Configuration.Fields.Item _
   ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "*****"

'設定フィールドを更新する
   gMail.Configuration.Fields.Update

'マイクロソフトのファイルダイアログボックスで送信するファイルを選択する
   Dim strFileToSend As String
   Dim dlgFile As FileDialog
   Dim strItem As Variant
   Dim nDlgResult As Long
   Set dlgFile = Application.FileDialog(msoFileDialogFilePicker)
'Excelとcsvファイルのみをフィルタリングする
   dlgFile.Filters.Add "Excelファイル", "*.csv; *.xls; *.xlsx; *.xlsm"
   nDlgResult = dlgFile.Show
   If nDlgResult = -1 Then
      If dlgFile.SelectedItems.Count > 0 Then
         For Each strItem In dlgFile.SelectedItems
             strFileToSend = strItem
         Next strItem
      End If
   End If
'メールプロパティと送信ファイルの設定
   With gMail
     .Subject = strSubject
     .From = "stitchmel@gmail.com"
     .To = strTo
     .TextBody = strBody
'選択したファイルを添付ファイルとして追加する
     .AddAttachment strFileToSend
   End With
'メール送信
   gMail.Send
   SendWorkbook= True
   Exit Function
eh:
   SendWorkbook = False
End Function

設定ポート25が機能しない場合、”The transport failed to connect to the server”というエラーが表示されることがあります。

上記の関数は、以下の手順で呼び出すことができます。

Sub SendMail()
   Dim strTo As String
   Dim strSubject As String
   Dim strBody As String
'変数に値を入れる
   strTo = "jon.smith@gmail.com"
   strSubject = "添付されたファイナンスファイルをご覧ください".
   strBody = "ここにメール本文のテキストが入ります"
'電子メールを送信する関数を呼び出す
   If SendWorkbook(strTo, strSubject, , strBody) = True Then
      Msgbox "電子メールの作成に成功しました"
   Else
      Msgbox "電子メールの作成に失敗しました！"
   End If
End Sub

送信するファイルを選択するように要求されます。 このコードは、フィルターが「*.csv; *.xls; *.xlsx; *.xlsm」であるため、Excelファイルに限定されていますが、Wordでもこのコードを使用でき、フィルターを適宜変更することができます。

ファイルを選択し、[開く]をクリックします。 メールの送信に成功すると、次のようなメッセージが表示されます。