Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 11-09-2018, 11:27 AM   #1
miacino
Newly Registered User
 
Join Date: Jun 2007
Posts: 63
Thanks: 10
Thanked 3 Times in 3 Posts
miacino is on a distinguished road
Attaching TWO reports into email

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

miacino is offline   Reply With Quote
Old 11-09-2018, 11:32 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,139
Thanks: 0
Thanked 683 Times in 668 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Attaching TWO reports into email

https://www.access-programmers.co.uk...d.php?t=302335
Ranman256 is offline   Reply With Quote
Old 11-09-2018, 11:35 AM   #3
theDBguy
Newly Registered User
 
theDBguy's Avatar
 
Join Date: Oct 2018
Posts: 110
Thanks: 1
Thanked 16 Times in 15 Posts
theDBguy is on a distinguished road
Re: Attaching TWO reports into email

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...

__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 11-09-2018, 11:36 AM   #4
1268
Newly Registered User
 
Join Date: Oct 2012
Posts: 43
Thanks: 1
Thanked 4 Times in 4 Posts
1268 is on a distinguished road
Re: Attaching TWO reports into email

Dim adfile1 as CDO.bodypart
Din adfile2 as CDO.bodypart

Set adfile1 = objsendmail.addattachment(yourattachmentfullfilena mewithpath)

Repeat for the second attachment.

Sent from my SM-G950U using Tapatalk
1268 is offline   Reply With Quote
Old 11-09-2018, 11:42 AM   #5
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 3,139
Thanks: 0
Thanked 683 Times in 668 Posts
Ranman256 will become famous soon enough Ranman256 will become famous soon enough
Re: Attaching TWO reports into email

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
Ranman256 is offline   Reply With Quote
Old 11-13-2018, 05:37 AM   #6
miacino
Newly Registered User
 
Join Date: Jun 2007
Posts: 63
Thanks: 10
Thanked 3 Times in 3 Posts
miacino is on a distinguished road
Re: Attaching TWO reports into email

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!
miacino is offline   Reply With Quote
Old 11-13-2018, 06:20 AM   #7
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,609
Thanks: 309
Thanked 404 Times in 389 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Attaching TWO reports into email

Quote:
Originally Posted by miacino View Post
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.

__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 11-13-2018, 07:03 AM   #8
miacino
Newly Registered User
 
Join Date: Jun 2007
Posts: 63
Thanks: 10
Thanked 3 Times in 3 Posts
miacino is on a distinguished road
Re: Attaching TWO reports into email

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
miacino is offline   Reply With Quote
Old 11-13-2018, 07:56 AM   #9
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,609
Thanks: 309
Thanked 404 Times in 389 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Attaching TWO reports into email

Almost

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 ?
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
miacino (11-13-2018)
Old 11-13-2018, 08:58 AM   #10
miacino
Newly Registered User
 
Join Date: Jun 2007
Posts: 63
Thanks: 10
Thanked 3 Times in 3 Posts
miacino is on a distinguished road
Re: Attaching TWO reports into email

Yes, thank you Gasman. I modified with the .pdf extension!
miacino is offline   Reply With Quote
Old 11-13-2018, 09:00 AM   #11
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 2,609
Thanks: 309
Thanked 404 Times in 389 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: Attaching TWO reports into email

Quote:
Originally Posted by miacino View Post
Yes, thank you Gasman. I modified with the .pdf extension!
And the variable name for the second file?
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
Old 11-13-2018, 09:02 AM   #12
miacino
Newly Registered User
 
Join Date: Jun 2007
Posts: 63
Thanks: 10
Thanked 3 Times in 3 Posts
miacino is on a distinguished road
Re: Attaching TWO reports into email

Yes, saw that I had the incorrect file name and modified that as well.

miacino is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Attaching two or more reports in one email mdg Reports 3 06-09-2011 09:52 PM
Attaching email address eipc General 0 03-22-2011 01:15 PM
Creating and attaching a PDF to an email mattie01 Reports 0 06-15-2006 06:26 PM
Attaching files to email Ethereal Modules & VBA 1 02-03-2006 09:10 PM
attaching files to email presuming_ed Modules & VBA 10 10-13-2003 07:26 AM




All times are GMT -8. The time now is 07:54 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World