HTML Email with PDF Attachment

Hi,

I've already got VBA code that using an exisitng MS Access report and puts it into the body of the email.

What i'm now looking to do is have another report attached to this email as a PDF?  Is this at all possible?

The current code I have (included below) generates a seperate email with the PDF attachement but I want to be able to add the PDF to the 1 email with the HTML report details?

Any assistance you can provide would be greatly appreciated.

Thx

Dim objOutlook          As Outlook.Application
Dim objOutlookMsg       As Outlook.MailItem
Dim objOutlookRecip     As Outlook.Recipient
Dim strReportName       As String
Dim strReportPath       As String
Dim strReportFullPath   As String
Dim stDocName           As String
Dim strMyString         As String
Dim strReportText       As String
Dim strTo               As String

    
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    With objOutlookMsg
        ' Add the To recipient(s) to the message. Substitute
        ' your names here.
        strTo = "TO details"
        Set objOutlookRecip = .Recipients.Add(strTo)
        objOutlookRecip.Type = olTo
        
        ' Add the CC recipient(s) to the message.
        strCC = "CC details"
        Set objOutlookRecip = .Recipients.Add(strCC)
        objOutlookRecip.Type = olCC
        
        ' Add the Subject to the message.
        strSubject = "Subject Text"
        .Subject = (strSubject)
                
        'Set the variables
        strReportPath = "Path to save report"
        strReportName = "Report Name"
        strReportFullPath = strReportPath & strReportName
            
            'Open the report, hidden an filtered to the current form record
            DoCmd.OpenReport strReportName, acViewPreview, , "id=" & Me.id, acHidden
            'Output the report as HTML
            DoCmd.OutputTo acOutputReport, strReportName, acFormatHTML, strReportFullPath & ".html"
            'Close the Report
            DoCmd.Close acReport, strReportName
        
            '--- Create a single line text file from the HTML Report,
            '--- in order to be displayed propely as an HTML Document in the Email body
            '--- Code modified from here:
            '--- http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21294504.html#a13177554
            'Open file for input (modification)
            Open strReportFullPath & ".HTML" For Input As #1
            Do While Not EOF(1)
                'Read each Line into strMyString variable.
                Line Input #1, strMyString
                strReportText = strReportText & strMyString     'Put all the Lines read into one String, FullText
            Loop
            Close #1
            
        'Insert the Full Report (as a simgle Line string) into the email body
        .HTMLBody = strReportText
        
        ' Add the attachment to the message
        stDocName = "Store Logins"
        DoCmd.SendObject acReport, stDocName, acFormatPDF

        ' Resolve each Recipient's name.
        For Each objOutlookRecip In .Recipients
            If Not objOutlookRecip.Resolve Then
                objOutlookMsg.Display
            End If
        Next
        
        'Send email without viewing it.
        '.Save
        '.Send
        
        'Dispay email before sending.
        .Display
        
    End With

'Cleanup Code
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Set objOutlookRecip = Nothing
End Sub

Open in new window

Chris_SizerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
You can add a attachment with this line anywhere in the With block for your Message object:

 .Attachments.Add("C:\YourFolder\YourReport.PDF")

Open in new window

0
mbizupCommented:
If you want to use a variable for your attachment:

Dim strReportText       As String
Dim strTo               As String
Dim strAttach as string   '<----*** Add this


strAttach = "C:\YourFolder\YourReport.PDF"   '<----*** Add this

    
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    With objOutlookMsg
           .Attachments.Add strAttach   '<----*** Add this

'  Etc....

Open in new window

0
Chris_SizerAuthor Commented:
Thanks for your reply mbizup!

My problem with this is that i need to generate a specific PDF for the particular email i'm sending which generates from another report in the database.

Is it then possible to have this PDF attached to the email instead of using a stored PDF?

Thx
0
mbizupCommented:
You'd do essentially the same thing you are doing with your first report, using the OutputTo command.  You'd have to save it first and then attach it:

Dim objOutlook          As Outlook.Application
Dim objOutlookMsg       As Outlook.MailItem
Dim objOutlookRecip     As Outlook.Recipient
Dim strReportName       As String
Dim strReportPath       As String
Dim strReportFullPath   As String
Dim stDocName           As String
Dim strMyString         As String
Dim strReportText       As String
Dim strTo               As String

'********* Add this
Dim strPDFReportName       As String
Dim strPDFReportPath       As String
Dim strPDFReportFullPath   As String

    
' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")
' Create the message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
    With objOutlookMsg
        ' Add the To recipient(s) to the message. Substitute
        ' your names here.
        strTo = "TO details"
        Set objOutlookRecip = .Recipients.Add(strTo)
        objOutlookRecip.Type = olTo
        
        ' Add the CC recipient(s) to the message.
        strCC = "CC details"
        Set objOutlookRecip = .Recipients.Add(strCC)
        objOutlookRecip.Type = olCC
        
        ' Add the Subject to the message.
        strSubject = "Subject Text"
        .Subject = (strSubject)
                
        'Set the variables
        strReportPath = "Path to save report"
        strReportName = "Report Name"
        strReportFullPath = strReportPath & strReportName

        '********* Add this
        strPDFReportPath = "Path to save PDF  report"
        strPDFReportName = "PDF Report Name"
        strPDFReportFullPath = strPDFReportPath & strReportName
           
            'Open the report, hidden an filtered to the current form record
            DoCmd.OpenReport strReportName, acViewPreview, , "id=" & Me.id, acHidden
            'Output the report as HTML
            DoCmd.OutputTo acOutputReport, strReportName, acFormatHTML, strReportFullPath & ".html"
            'Close the Report
            DoCmd.Close acReport, strReportName

'**** Do the same for your PDF report

            DoCmd.OpenReport strPDFReportName, acViewPreview, , "id=" & Me.id, acHidden
            'Output the report as PDF
            DoCmd.OutputTo acOutputReport, strPDFReportName, acFormatPDF, strPDFReportFullPath & ".html"
            'Close the Report
            DoCmd.Close acReport, strPDFReportName

           .Attachments.Add strPDFReportFullPath '<----*** Add the attachment


        
            '--- Create a single line text file from the HTML Report,
            '--- in order to be displayed propely as an HTML Document in the Email body
            '--- Code modified from here:
            '--- http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21294504.html#a13177554
            'Open file for input (modification)
            Open strReportFullPath & ".HTML" For Input As #1
            Do While Not EOF(1)
                'Read each Line into strMyString variable.
                Line Input #1, strMyString
                strReportText = strReportText & strMyString     'Put all the Lines read into one String, FullText
            Loop
            Close #1
            
        'Insert the Full Report (as a simgle Line string) into the email body
        .HTMLBody = strReportText
        
        ' Add the attachment to the message
        stDocName = "Store Logins"
        DoCmd.SendObject acReport, stDocName, acFormatPDF

        ' Resolve each Recipient's name.
        For Each objOutlookRecip In .Recipients
            If Not objOutlookRecip.Resolve Then
                objOutlookMsg.Display
            End If
        Next
        
        'Send email without viewing it.
        '.Save
        '.Send
        
        'Dispay email before sending.
        .Display
        
    End With

'Cleanup Code
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
Set objOutlookRecip = Nothing
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.