VBA Script to Email Specific Parts of Report to Specific Email Addresses (1 Viewer)

ShadowFox · May 1, 2012

I've been searching for an answer to my problem for a few days now, and I haven't come up with anything that will work for my needs...it may not even be possible.

What I'd like to do is to write a VBA script that emails a report of potential customer leads to our 10 regional offices in separate emails with attachments for each of the offices, with only the leads that pertain to their location.

Is this possible? If so, sample scripts would be greatly appreicated.

Thanks in advance!

PS: I'm using MS Office 2010.

pbaldy · May 1, 2012

See if this and the related looping link get you started.

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

ShadowFox · May 1, 2012

pbaldy said:
See if this and the related looping link get you started.

Thanks for the reply. I was able to get the email to work, but how do I incorporate the creation of the customized report as the attachment?

I'm trying to post the code I've put together so far, but for some reason the forum won't let me do it because it says it contains email addresses...which it doesn't because I have removed all filepaths and email addresses.

pbaldy · May 1, 2012

If you use SendObject, it should simply attach the report (the code in that link would filter it). Other methods would require you to export the report and then attach it.

ShadowFox · May 1, 2012

pbaldy said:
If you use SendObject, it should simply attach the report (the code in that link would filter it). Other methods would require you to export the report and then attach it.

Would I be able to use a filter on the report (see below) when using the SendObject command?

Me.Filter = "ShipmentsID=" & Forms![Select Load List]![LoadID]
Me.FilterOn = True

I'll post the code I've put together already, as soon as the forum allows me...I have to reach 10 posts before I can.

pbaldy · May 1, 2012

Yes, that code will filter it for SendObject. You just need to make sure you set the value of that form control first.

I thought the forum only stopped you from posting links until you got to 10 posts. Code should be fine.

ShadowFox · May 1, 2012

pbaldy said:
Yes, that code will filter it for SendObject. You just need to make sure you set the value of that form control first.

I thought the forum only stopped you from posting links until you got to 10 posts. Code should be fine.

I really want to post the code so you can show me where to insert those commands. The forum keeps telling me that I can't post email addresses, but my code doesn't contain any emails, I've removed all personal information.

3 more to go!

pbaldy · May 1, 2012

That's silly. You can email it to me if you want

deleted to prevent spam

ShadowFox · May 1, 2012

pbaldy said:
That's silly. You can email it to me if you want

pbaldy
gmail
com

Email sent...

pbaldy · May 1, 2012

The code for anyone following:

Code:

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
  ' 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_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("File_Path_Here")
        
        .Send
      End With
      MyRS.MoveNext
   Loop
   Set objOutlookMsg = Nothing
   Set objOutlook = Nothing
End Sub

To use that method you would add a line:

Forms!FormName.TextboxName = MyRS!FieldName

adjusting the names so that the report code can find use the form textbox to filter itself. Then since you're using automation you'd need to create a file. I typically use

DoCmd.OutputTo ...

One of the arguments for that is a path, which you would turn around and use for the attachment path.

ShadowFox · May 1, 2012

pbaldy said:
The code for anyone following:
To use that method you would add a line:

Forms!FormName.TextboxName = MyRS!FieldName

adjusting the names so that the report code can find use the form textbox to filter itself. Then since you're using automation you'd need to create a file. I typically use

DoCmd.OutputTo ...

One of the arguments for that is a path, which you would turn around and use for the attachment path.

Would the Forms!FormName.TextboxName = MyRS!FieldName be put after the "Do Until MyRS.EOF" line?

pbaldy · May 1, 2012

Sorry, yes. Also the OutputTo line (or whatever method you choose to create a file). Basically anywhere after that line and the line that attaches the file to the email. Personally, I'd probably do it right after, and include a test to be sure the attachment file got created properly (I use the Dir() function).

ShadowFox · May 2, 2012

pbaldy said:
Sorry, yes. Also the OutputTo line (or whatever method you choose to create a file). Basically anywhere after that line and the line that attaches the file to the email. Personally, I'd probably do it right after, and include a test to be sure the attachment file got created properly (I use the Dir() function).

Okay, I'm working on putting this in the script to output the file. I chose to use the DoCmd.OutputTo method. How do I put a custom name on the output file? I've tried doing this in the past, but haven't been able to succesfully implement it. I'd like to insert the current date and a field from the form (the office name).

This is post number 10, so I'll be able to post the script after this question is answered.

Thanks!

pbaldy · May 2, 2012

You can build a name from values on the form, from the recordset, etc:

strFileName = "Whatever" & Me.OfficeName & Format(Date(), "yyyymmdd")
DoCmd.OutputTo acOutputReport, "ReportName", acFormatPDF, "C:\intacc\" & strFileName & ".pdf"

ShadowFox · May 2, 2012

pbaldy said:
You can build a name from values on the form, from the recordset, etc:

strFileName = "Whatever" & Me.OfficeName & Format(Date(), "yyyymmdd")
DoCmd.OutputTo acOutputReport, "ReportName", acFormatPDF, "C:\intacc\" & strFileName & ".pdf"

Ok, this is what I have so far. I'm a little unclear on how to implement the filter on the report. I just copied and pasted the code from the site you referenced into the script below.

Code:

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
 
  'This applies a filter to the report to limit it to the specific location
  Me.Filter = "OFFICE_ID=" & Forms![Select Load List]![LoadID]
  Me.FilterOn = True
 
  '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 · May 2, 2012

These lines:

'This applies a filter to the report to limit it to the specific location
Me.Filter = "OFFICE_ID=" & Forms![Select Load List]![LoadID]
Me.FilterOn = True

go in the open event of the report, not here. It lets the report filter itself based on the value in the form. The form and textbox names need to be changed to something of yours. In this code you'd have a line placing the current value from the recordset into that textbox. An alternative is to use a public variable instead of a form control. You also need to declare strFileName.

ShadowFox · May 2, 2012

pbaldy said:
These lines:

'This applies a filter to the report to limit it to the specific location
Me.Filter = "OFFICE_ID=" & Forms![Select Load List]![LoadID]
Me.FilterOn = True

go in the open event of the report, not here. It lets the report filter itself based on the value in the form. The form and textbox names need to be changed to something of yours. In this code you'd have a line placing the current value from the recordset into that textbox. An alternative is to use a public variable instead of a form control. You also need to declare strFileName.

Ok, back the truck up for a second. That went over my head a little bit, LOL!

How do I structure the open report event?

I understand I need to change the names of things, I just post the code in generals for security reasons.

I was thinking of using a form to control the filter on the report. The form would have the following fields: OFFICE_ID, EMAIL_ADDRESS, and CC_ADDRESS.

Am I making any sense?

pbaldy · May 2, 2012

Where did I lose you?

To be filtered for export, the report has to filter itself via some method, this is one. The lines I quoted need to be in the report's open event, not this procedure. You would replace them with:

Forms!FormName.TextboxName = MyRS!OFFICE_ID

Basically in your loop you're putting a value into that form that the report can use to filter itself when you export it.

ShadowFox · May 2, 2012

pbaldy said:
Where did I lose you?

To be filtered for export, the report has to filter itself via some method, this is one. The lines I quoted need to be in the report's open event, not this procedure. You would replace them with:

Forms!FormName.TextboxName = MyRS!OFFICE_ID

Basically in your loop you're putting a value into that form that the report can use to filter itself when you export it.

Could you insert this action into the code I've posted?

pbaldy · May 2, 2012

You'd put it where the 3 lines I said to take out are, right before the DoCmd.OutputTo line.

VBA Script to Email Specific Parts of Report to Specific Email Addresses (1 Viewer)

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Registered User.

Wino Moderator

Similar threads

Users who are viewing this thread