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


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!

      .Send

    

   End With

 

  

   info = "Sending CDS Spreadsheet"

   Application.StatusBar = info

  

   Set objOutlookMsg = Nothing

 

 

   info = "Done sending CDS Spreadsheet"

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

 

 

endit:

   

    ActiveWorkbook.Saved = True

    Application.Quit

    Exit Sub

 

 

err_handler:

    info = "Problem sending HK ADBI daily reports"

       

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

   

    Resume endit

 

End Sub