​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking

Sending emails from outlook via VBA

Here's an example of sending emails using the com automation of outlook from within VBA. We are sending a spreadsheet to a bunch of recipients who are contained in cell A1 of this XL. You need to set a reference to Microsoft Outlook first.

Sub test()


   Dim objOutlook As Outlook.Application

   Dim objOutlookMsg As Outlook.MailItem

   Dim objOutlookRecip As Outlook.Recipient

   Dim objOutlookAttach As Outlook.Attachment

   Dim targetfullfilename As String


   Application.WindowState = xlMinimized


   commandstr = "cmd /c net send "


   On Error GoToerr_handler


   x = Format(Now(), "yymmdd")


' Create the Outlook session.

   Set objOutlook = CreateObject("Outlook.Application")


' Create the message.

   Set objOutlookMsg = objOutlook.CreateItem(olMailItem)


   With objOutlookMsg



      targetfullfilename = "j:\glen\java_bin\cdssheet.xls"


      Set objOutlookAttach = .Attachments.Add(targetfullfilename)


' Set the Subject, Body, recipients and Importance of the message.


      .Subject = "CDS SpreadSheet"

      .Body = "Updated CDS spreadsheet attached"

      .Importance = olImportanceNormal


      to_recips = Cells(1, 1)

      .To = to_recips



' send that puppy!



   End With



   info = "Sending CDS Spreadsheet"

   Application.StatusBar = info


   Set objOutlookMsg = Nothing



   info = "Done sending CDS Spreadsheet"

   retval = Shell(commandstr & "EUEDWD08585 " & info, vbHide)





    ActiveWorkbook.Saved = True


    Exit Sub




    info = "Problem sending HK ADBI daily reports"


    retval = Shell(commandstr & "EUEDWD08585 " & info, vbHide)


    Resume endit


End Sub