VBA Script to Email Specific Parts of Report to Specific Email Addresses

[ShadowFox]

How does this look?

Option Compare Database
Option Explicit
Sub SendMessages(Optional AttachmentPath)
  Dim MyDB As Database
  Dim MyRS As Recordset
  Dim objOutlook As Outlook.Application
  Dim objOutlookMsg As Outlook.MailItem
  Dim objOutlookRecip As Outlook.Recipient
  Dim objOutlookAttach As Outlook.Attachment
  Dim TheAddress As String
  Set MyDB = CurrentDb
  Set MyRS = MyDB.OpenRecordset("tblMailingList")
  MyRS.MoveFirst
  ' Create the Outlook session.
  Set objOutlook = CreateObject("Outlook.Application")
 
  Do Until MyRS.EOF
  'Filter report to display only Supplier currently showing on FORM_NAME
  DoCmd.OpenReport "REPORT_NAME", acViewPreview, , "OFFICE_ID = " & OFFICE_ID
 
  'This outputs the filtered report in .RTF format to temporary location.
  strFileName = "REPORT_NAME" & Me.OfficeName & Format(Date, "yyyymmdd")
  DoCmd.OutputTo acOutputReport, "REPORT_NAME", acFormatPDF, "C:\FILE\PATH\" & strFileName & ".rtf"
 
  ' Create the e-mail message.
  Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    TheAddress = MyRS![Email Address]
     With objOutlookMsg
        ' Add the To recipients to the e-mail message.
        Set objOutlookRecip = .Recipients.Add(TheAddress)
        objOutlookRecip.Type = olTo
        ' Add the Cc recipients to the e-mail message.
           Set objOutlookRecip = .Recipients.Add("EMAIL_ADDRESSES_GO_HERE")
           objOutlookRecip.Type = olCC
 
        ' Set the Subject, the Body, and the Importance of the e-mail message.
        .Subject = "Testing Mass Emails"
        .Body = "Testing body text"
 
        'Add the attachment to the e-mail message.
        Set objOutlookAttach = .Attachments.Add("C:\FILE\PATH\" & strFileName & ".rtf")
 
        .Send
      End With
      MyRS.MoveNext
   Loop
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
End Sub

 

[pbaldy]

If it works, it looks great. If it doesn't work, it doesn't look so good.

It looks like you decided to filter the report a different way? That method should work, but I don't like it because of the overhead of opening and closing (hint hint) the report for every loop.

 

[ShadowFox]

If it works, it looks great. If it doesn't work, it doesn't look so good.

It looks like you decided to filter the report a different way? That method should work, but I don't like it because of the overhead of opening and closing (hint hint) the report for every loop.

How do I do it without open and closing the report? I get it, if I keep it this way, I need to close the report just before the end of the loop.

 

[pbaldy]

I would do it the way I showed you, with the filtering code in the open event of the report. At this point, I'd stick with what you're doing now, adding the code to close the report (otherwise you'll get copies of the first report sent out to everybody).

 

[ShadowFox]

I would do it the way I showed you, with the filtering code in the open event of the report. At this point, I'd stick with what you're doing now, adding the code to close the report (otherwise you'll get copies of the first report sent out to everybody).

This will make you laugh...or cringe. What's the close command? LOL!

 

[pbaldy]

How about...Close?

DoCmd.Close appropriate arguments here

 

[ShadowFox]

How about...Close?

DoCmd.Close appropriate arguments here

Like so?

DoCmd.CloseReport acReport, "REPORT_NAME", acSaveNo

 

[pbaldy]

It's not what I suggested, but hey, if it works, go with it!