Attaching TWO reports into email (1 Viewer)

miacino

Registered User.
Local time
Today, 08:29
Joined
Jun 5, 2007
Messages
106
I've probably exceeded my questions for the week, and maybe my brain is just fried (thank goodness its Friday!)....
I have the attached code which sends an email from my database via Outlook. Where/how do I go about attaching two reports from my database to the email as pdf files?

-------
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

With MailOutLook
.to = [text386l]
.Subject = "New Position Information"
.cc = [Text388]
.HTMLBody = "<HTML><BODY><font face=Calibri>Good Afternoon Dr. " & [Text412] & ","
.HTMLBody = .HTMLBody & "<BR><BR><b>-Employee: </b>" & [PhysicianHired]
.HTMLBody = .HTMLBody & "<BR><b>-Department: </b>" & [Division]
.HTMLBody = .HTMLBody & "<BR><b>-Manager: </b>" & [Text343]
.HTMLBody = .HTMLBody & "<BR><b>-Proposed Start Date: </b>”
.HTMLBody = .HTMLBody & "<HTML><BODY><font face=Calibri><BR><BR>" & [Text416]
.Display

End With
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:29
Joined
Oct 29, 2018
Messages
21,359
Hi,

Maybe something like

[ code]
With MailOutlook
...
DoCmd.OutputTo acOutputReport, “ReportName”, acFormatPDF, “C:\Filename.pdf”
.Attachments.Add “C:\Filename.pdf”
‘repeat for 2nd report
... rest of existing code here
.Display
...
[/code]
Hope it helps...
 

1268

Registered User.
Local time
Today, 10:29
Joined
Oct 11, 2012
Messages
44
Dim adfile1 as CDO.bodypart
Din adfile2 as CDO.bodypart

Set adfile1 = objsendmail.addattachment(yourattachmentfullfilenamewithpath)

Repeat for the second attachment.

Sent from my SM-G950U using Tapatalk
 

Ranman256

Well-known member
Local time
Today, 11:29
Joined
Apr 9, 2015
Messages
4,339
Code:
Public Sub testEmail()
Dim vTo, vSubj, vBody
Dim colFiles As New Collection

vTo = "WileE@acme.com"
vSubj = "test multi attachs"
vBody = "Dear Wiley"

colFiles.Add "c:\folder\file1.txt"
colFiles.Add "c:\folder2\file2.xls"
'or 
'use the FILE EXPLORER to pick X number of files

Set colFiles = UserPickFiles("c:\temp")
Call Send1Email(vTo, vSubj, vBody, colFiles)

Set colFiles = Nothing
End Sub


'-------------------
Public Function Send1Email(ByVal pvTo, ByVal pvSubj, ByVal pvBody, Optional pcolFiles As Collection) As Boolean
'-------------------
Dim oApp As Outlook.Application
Dim oMail As Outlook.MailItem
dim vFile

On Error GoTo ErrMail

Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(olMailItem)

With oMail
    .To = pvTo
    .Subject = pvSubj
    .Body = pvBody

    If Not IsEmpty(pcolFiles) Then
        For Each vFile In pcolFiles
             .Attachments.Add vFile, olByValue, 1
        Next
    End If
    
   .Send
End With
Send1Email = True

endit:
Set oMail = Nothing
Set oApp = Nothing
Exit Function

ErrMail:
MsgBox Err.Description, vbCritical, Err
Resume endit
Resume Next
End Function


'-------------------
Public Function UserPickFiles(ByVal pvStartDir)
'-------------------
Dim strTable As String
Dim strFilePath As String
Dim sDialog As String, sDecr  As String, sExt As String
Dim fD As FileDialog
Dim vFile
Dim colFiles As New Collection

Set fD = Application.FileDialog(msoFileDialogFilePicker)   '<----!!  MUST ADD REFERENCE : Microsoft Office 11.0 Object Library
With fD
    .AllowMultiSelect = True
    .Title = "Locate a file to Import"
    .ButtonName = "Import"
    .Filters.Clear
    .Filters.Add "All Files", "*.*"
    .InitialFileName = pvStartDir
    .InitialView = msoFileDialogViewList    'msoFileDialogViewThumbnail
    .AllowMultiSelect = True
     
        If .show = 0 Then
           'There is a problem
           Exit Function
        End If
    
           'Save the first file selected
           
        For Each vFile In .SelectedItems
           colFiles.Add vFile
        Next
    
End With
Set UserPickFiles = colFiles
'Set fD = Nothing
End Function
 

miacino

Registered User.
Local time
Today, 08:29
Joined
Jun 5, 2007
Messages
106
I'm just looking to attach two reports from my database, not files on my computer. Using my above code, could someone direct me how to attach two database reports?
Thanks!
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:29
Joined
Sep 21, 2011
Messages
14,056
I'm just looking to attach two reports from my database, not files on my computer. Using my above code, could someone direct me how to attach two database reports?
Thanks!

I think you'll find that you need to save them as files first to do this.
 

miacino

Registered User.
Local time
Today, 08:29
Joined
Jun 5, 2007
Messages
106
RESOLVED:

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
FileName = Application.CurrentProject.Path & "\RAS Info"
DoCmd.OutputTo acReport, "RAS INFO", acFormatPDF, FileName, False
FileName2 = Application.CurrentProject.Path & "\PM Onboarding Checklist_PerDiem"
DoCmd.OutputTo acReport, "PM Onboarding Checklist_PerDiem", acFormatPDF, FileName, False
With MailOutLook
.to = [Text386]
.Subject = "New Position Information"
.cc = [Text388]
.HTMLBody = "<HTML><BODY><font face=Calibri>Good Afternoon Dr. " & [Text412] & ","
.HTMLBody = .HTMLBody & "<BR><BR>Please find attached, for your records, the EE#, RAS instructions and Onboarding Checklist for your new hire:"
.HTMLBody = .HTMLBody & "<BR><BR><b>- Employee: </b>" & [PhysicianHired]
.HTMLBody = .HTMLBody & "<BR><b>- Department: </b>" & [Division]
.HTMLBody = .HTMLBody & "<BR><b>- Start Date: </b>" & [Actual Start Date]
.HTMLBody = .HTMLBody & "<BR><b>- Job Title: </b>" & [Text302]
.HTMLBody = .HTMLBody & "<BR><b>- Employee#: </b>" & [employee#]
.HTMLBody = .HTMLBody & "<HTML><BODY><font face=Calibri><BR><BR>" & [Text416]
.Attachments.Add FileName
.Attachments.Add FileName2
.Display
End With

If Not IsNull(Me![OnboardingChecklist]) Or IsNull(Me![OnboardingChecklist]) Then
Me![OnboardingChecklist] = Date

End If
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:29
Joined
Sep 21, 2011
Messages
14,056
Almost :D

If you do not put .pdf extensions on the filenames, the recipient will be asked what program to use to open the files.?

Also you are overwriting the first report with the second report as you are using the same variable Filename ?
 

miacino

Registered User.
Local time
Today, 08:29
Joined
Jun 5, 2007
Messages
106
Yes, thank you Gasman. I modified with the .pdf extension!
 

miacino

Registered User.
Local time
Today, 08:29
Joined
Jun 5, 2007
Messages
106
Yes, saw that I had the incorrect file name and modified that as well.
 

Users who are viewing this thread

Top Bottom