Automate *.pdf (multiple reports) to send from Access thru MS Outlook

[pnevilm]

I am trying to automate sending daily reports to a select list of internal employees, and Access 2007 is only letting me send one at a time, not multiple reports in a single email.

Not only do I have this problem, but, each time I try to send one usingthe macro function (this is one report for one email) MS Outlook asks me to accept/deny this priviledge. How do I get around this, so that I can automate the sending of multiple reports, and override the security of MS Outlook that forces me to accept/deny sending the email? My boss would like me to send out the reports on his behalf as if it is from his email box? Can this be done?

 

[the_net_2.0]

do you have Access 2007? If you do, and you have Adobe Acrobat, did you know that you can interface those two? I think too, that you can merge more than one object in a single pdf file with that.

I can't remember if it's an addin, or if it automatically appears in the tools ribbon in 07. If not though, for the security issue, check the FAQ section here for that message. I think I saw something in there that covered that.

 

[Trevor G]

I am trying to automate sending daily reports to a select list of internal employees, and Access 2007 is only letting me send one at a time, not multiple reports in a single email.

Not only do I have this problem, but, each time I try to send one usingthe macro function (this is one report for one email) MS Outlook asks me to accept/deny this priviledge. How do I get around this, so that I can automate the sending of multiple reports, and override the security of MS Outlook that forces me to accept/deny sending the email? My boss would like me to send out the reports on his behalf as if it is from his email box? Can this be done?

Take a look at this sample code, it will send multiple reports in one message, you have to set the reference to outlook in your VBA screen, to do this use Alt + F11 on the keyboard then go to the Tools and then References scroll down the list until you find Microsoft Outlook Object Library 12, tick the box. Copy and paste the code into a new module sheet, so then go to the Insert Menu and select Module

In the code you will have to change the report names and file locations.

Function sndrpt()
Rem <!-- Make sure the Microsoft Object xx.0 Reference Library is enabled & _
(found under Tools>References in the VBA Editor) -->

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim strAttach1 As String
Dim strAttach2 As String
Dim strAttach3 As String
Dim strAttach4 As String
Dim strAttach5 As String

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

'Output Reports
Rem <!-- change "Reportx" to match the report names you wish to export. & _
IMPORTANT: Make sure the location you select to save your reports to exists, Access will & _
not create the folders for you. -->
DoCmd.OutputTo acOutputReport, "Report1", acFormatRTF, "C:\YourFolder\Report1.rtf", False
DoCmd.OutputTo acOutputReport, "Report2", acFormatRTF, "C:\YourFolder\Report1.rtf", False
DoCmd.OutputTo acOutputReport, "Report3", acFormatRTF, "C:\YourFolder\Report1.rtf", False
DoCmd.OutputTo acOutputReport, "Report4", acFormatRTF, "C:\YourFolder\Report1.rtf", False
DoCmd.OutputTo acOutputReport, "Report5", acFormatRTF, "C:\YourFolder\Report1.rtf", False

'Set Attachments
Rem <!-- make sure to correlate the attachments to each of the reports you wish to send -->
strAttach1 = "C:\YourFolder\Report1.rtf"
strAttach2 = "C:\YourFolder\Report2.rtf"
strAttach3 = "C:\YourFolder\Report3.rtf"
strAttach4 = "C:\YourFolder\Report4.rtf"
strAttach5 = "C:\YourFolder\Report5.rtf"

'Generate email
With objEmail
.To = "email@removed.com"
.Subject = "Your subject here"
.Body = "Message in body of email here"
.Display
.Attachments.Add strAttach1
.Attachments.Add strAttach2
.Attachments.Add strAttach3
.Attachments.Add strAttach4
.Attachments.Add strAttach5
End With

'Remove attachments from drive
Kill strAttach1
Kill strAttach2
Kill strAttach3
Kill strAttach4
Kill strAttach5

End Function

Once you have done your amendments you can then run the code from a button on a form or use the VBA

 

[darbid]

My boss would like me to send out the reports on his behalf as if it is from his email box? Can this be done?

I do not think this has been answered. He needs to give you the right to send emails on his behalf. He has to do this from his outlook client.

This this here will have to be changed

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

dim objOutlook as Outlook.Application
Dim objNS As Outlook.Namespace
Dim objFolder As Outlook.MAPIFolder 'get name of other persons folder
Dim objRecip As Outlook.Recipient 'other persons name
Dim strName As String 'the name or email of the persons folder

On Error Resume Next

' ### name of person whose Calendar you want to use or email address or SMTP ###
strName = str_app_user

Set objOutlook = CreateObject("Outlook.application")

Set objNS = objOutlook.GetNamespace("MAPI")

Set objRecip = objNS.CreateRecipient(strName)

'objRecip.Resolve  'will raise a security message - not really needed

Set objFolder = objNS.GetSharedDefaultFolder(objRecip, olFolderInbox)

If objFolder Is Nothing Then
    msgbox "Problem"
End If

Set objEmail = objFolder.Items.Add(olPostItem)

Make sure you set all Outlook objects to nothing as soon as possible and always set the Outlook Application object last to nothing so that Outlook closes properly.

 

[Mr. Southern]

Take a look at this sample code, it will send multiple reports in one message, you have to set the reference to outlook in your VBA screen, to do this use Alt + F11 on the keyboard then go to the Tools and then References scroll down the list until you find Microsoft Outlook Object Library 12, tick the box. Copy and paste the code into a new module sheet, so then go to the Insert Menu and select Module

In the code you will have to change the report names and file locations.



Once you have done your amendments you can then run the code from a button on a form or use the VBA

I know this post is very old but I was attempting to do the same thing and came across an issue. When I try to run the mod I get a runtime errorRuntime error '-2147024894(80070002)'. The debug highlights

.Attachments.Add strAttach1

It does not appear to be exporting to the file location. If I send the file via another Module/Macro before this one it works.

Any help would be appreciated.