VBA to send multiple reports as attachments in .rtf file format


Hi all,

I have the following code (taken from a post found in the search facility):

Dim appOutl As Object
Dim MyNameSpace As Object
Dim myemail As Object
Set appOutl = CreateObject("Outlook.Application")
Set MyNameSpace = appOutl.getNameSpace("MAPI")
Set myemail = appOutl.CreateItem(0)


myemail.to = "someone@someone.com"
myemail.Subject = "THIS IS AN AUTOMATED TEST"
myemail.body = "THIS IS THE BODY OF THE EMAIL MESSAGE"
myemail.Attachments.Add "c:something.txt", 1, 5520, "frunlog.txt"
myemail.Send

I was wondering how I attach a report in ".rtf" format to this email?

For example, a report that I would like to attach I might open from a command button with the following code:

DoCmd.OpenReport "Drivers Report", acPreview, "[ID] = " & Me.ID

I would want to attach this report (filtered with the link criteria) as an rtf attachment to my email message.

I have also noticed that outlook will not automatically send the email, it will remain in the outbox until I click send and receive within outlook. Is there any way to get around this?

Thanks for any help....

Peter


Sponsored Links:



Hi, I am a beginner MS Access user.

I use 5 "SendObject" actions in a Macro to send 5 reports in .rtf file type (report1.rtf , report2.rtf, etc..) to a recipient. This method results in multiple emails being sent to the recipient.

I am wondering if there is a way to attach all of the reports (*.rtf's) in a single email so that there will be only 1 email sent to the recipient. Any help is greatly appreciated.

Thanks




I have a report that gets passed a paramter value when opened.

I am current trying to send the report as an attchment, but because the report must have the parameter value and the DoCmd.SendObject doesn't seem to have a place for an arg... I have to first open the report, then try to send it... like so:


	Code:
	DoCmd.OpenReport "00100_Rpt_Document(Draft)", acViewPreview, , , , intAgreementID
        DoCmd.SendObject acSendReport, "00100_Rpt_Document(Draft)", acFormatPDF, "gary.black@xxx.gov", , , "Sending A
Report", "This is the Access Report"
        DoCmd.Close acReport, "00100_Rpt_Document(Draft)"

When I do this I get an error message stating: Quote: No profiles have been created. To create a new profile, use the Mail icon in the Control Panel. And if I try to use just the SendObject command Access just hangs:


	Code:
	        DoCmd.SendObject acSendReport, "00100_Rpt_Document(Draft)", acFormatPDF, "gary.black@xxx.gov", , , "Sending A
Report", "This is the Access Report"


I have seen two post on this site pertaining to the error message I posted, but no one ever responded to them, which doesn't bode well I guess


Thanks for any help,
Gary




I am using SendObject to email a report via Microsoft Exchange. It looks like this:

DoCmd.SendObject acSendReport, "ReportName", "SnapshotFormat(*.snp)", ... etc.

It appears that I can only send one report per email. Is there a way to send multiple reports in the same email?

Thanks in advance.




I have Acrobat PDFMaker.

In Access' file menu, there is a menu called Adobe PDF and under it, it allows you to merge multiple reports into a single PDF file.

How can I create a button that can do this?

Thanks in advance.




Hey guys

Scenario: User is looking at a job details. Job has pictures and PDF's as external files saved in the filesystem of the computer. User clicks Email Invoice. System attaches report as a PDF and opens Outlook Email message with email address, subject and body filled and the report PDF as an attachment

I can currently send a report as an attachment in an Outlook Email by using the "DoCmd.SendObject acSendReport" code.

That works nicely for me because it attaches the report I specify but has the limitation that I cannot attach any other objects.

I therefore came across the following code which allows me to add multiple attachments to an Outlook email message:


	Code:
	Sub CreateEmail()

'write the default Outlook contact name list to the active worksheet

Dim OlApp As Object
Dim OlMail As Object
Dim ToRecipient As Variant
Dim CcRecipient As Variant

Set OlApp = CreateObject("Outlook.Application")
Set OlMail = OlApp.createitem(olmailitem)

For Each ToRecipient In Array("User 1", "User 2", "User 3")
OlMail.Recipients.Add ToRecipient
Next ToRecipient

For Each CcRecipient In Array("User 4", "User 5", "User 6")
With OlMail.Recipients.Add(CcRecipient)
.Type = olCC
End With
Next CcRecipient

'fill in Subject field
OlMail.Subject = "Test of Outlook email"

'Add the active workbook as an attachment
OlMail.Attachments.Add "C:UsersUserPicturesUnspecified1.jpg"
OlMail.Attachments.Add "C:UsersUserPicturesUnspecified2.jpg"

'Display the message
OlMail.Display 'change this to OlMail.Send if you just want to send it without previewing it

End Sub

The problem with the above is that I cannot figure out how to attach a report of my choice. I don't know if there is a way to tell access to open a report and attach it as a PDF.

Worst comes to worst, I'll try to figure out a way to save the report as a PDF and then attach that as one of the attachments.




Hey guys

Scenario: User is looking at a job details. Job has pictures and PDF's as external files saved in the filesystem of the computer. User clicks Email Invoice. System attaches report as a PDF and opens Outlook Email message with email address, subject and body filled and the report PDF as an attachment

I can currently send a report as an attachment in an Outlook Email by using the "DoCmd.SendObject acSendReport" code.

That works nicely for me because it attaches the report I specify but has the limitation that I cannot attach any other objects.

I therefore came across the following code which allows me to add multiple attachments to an Outlook email message:

Code: Sub CreateEmail() 'write the default Outlook contact name list to the active worksheet Dim OlApp As Object Dim OlMail As Object Dim ToRecipient As Variant Dim CcRecipient As Variant Set OlApp = CreateObject("Outlook.Application") Set OlMail = OlApp.createitem(olmailitem) For Each ToRecipient In Array("User 1", "User 2", "User 3") OlMail.Recipients.Add ToRecipient Next ToRecipient For Each CcRecipient In Array("User 4", "User 5", "User 6") With OlMail.Recipients.Add(CcRecipient) .Type = olCC End With Next CcRecipient 'fill in Subject field OlMail.Subject = "Test of Outlook email" 'Add the active workbook as an attachment OlMail.Attachments.Add "C:UsersUserPicturesUnspecified1.jpg" OlMail.Attachments.Add "C:UsersUserPicturesUnspecified2.jpg" 'Display the message OlMail.Display 'change this to OlMail.Send if you just want to send it without previewing it End Sub The problem with the above is that I cannot figure out how to attach a report of my choice. I don't know if there is a way to tell access to open a report and attach it as a PDF (in the above code).

Worst comes to worst, I'll try to figure out a way to save the report as a PDF (to the local filesystem) and then attach that as one of the attachments.

Can anybody help me or guide me in the right direction please?

Thanks




I have a form with user contact info and other stuff that I need to track where I have set up a button to add the user to Outlook, a button to send them an email, and a button filtered by their user ID so that I can generate an individual report on the form for each user. I can create a .pdf version of the report and email it but I either have to save it and use the button to open the email then manually attach the report or I can email it to the person directly from the .pdf version but I have to then add their email address. Either method takes a lot of time to complete which adds up when I have multiple reports to multiple users that need sent out at once.

Is there a way to combine this into one smooth operation where I can use VBA to take the report, use the email address and the user ID as criteria to generate individual reports that will loop through and email the correct report to the correct user automatically?

Typically I have to generate the report based on set criteria then mail a hard copy via snail mail each year to people who still have outstanding classs that they need to complete. This is not critical but I just want to know if it can be done.




Hi,

I'm trying to send report called "Harmonogram" as attachment in e-mail. I don't want to use send object method because sometimes I need to sent more files as attachment. Here is the code I use:


	Code:
	Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)
Set thereport = CurrentDb.OpenRecordset("Harmonogram")

Email = Me.Email

With objEmail
.To = Email
.Subject = Harmonogram
.Body = text
.Attachments.Add thereport, olByReference, 1, "Harmonogram"
.Display
End With

Set objEmail = Nothing

I have found this code on Internet but there is a problem in line: .Attachments.Add thereport, olByReference, 1, "Harmonogram". It shows me an error message: Data type conversion error. - 3421. Any ideas?

Thank for help.




My db uses a command buttom to send e-mail by Outlook.
The following code selects the record to preview then e-mail.
I'd like to be able to select multiple records as attachments on the one e-mail.
I suppose I'd need additional field(s) on the form to select the records but I'm not sure how to write this.
Code or threads appreciated.

DoCmd.OpenReport "email report", acViewPreview, , "[ID]=" & Forms![data]![ID]
DoCmd.SendObject acReport, stDocName, acFormatRTF, , "to.you@company.co.uk","copytome@business.co.u k , "E-mail subject", "There are some attachments (hopefully) with this e-mail", True

Thanks




Hi, I am using VBA to create a report and then write my data from my recordsource from. Does anyone know how I would write a line in the Page Header section? I'm using MS Access 2010. here is my code for my Page Header and I want to put a line at the bottom of the Page Header:

Private Sub RunReport_Click()
Dim db As Database ' database object
Dim rs As Recordset ' recordset object
Dim sSQL As String
Dim fld As Field ' recordset field
Dim txtNew As Access.TextBox ' textbox control
Dim lblNew As Access.Label ' label control
Dim lblSub As Access.Label ' label control
Dim rpt As Report ' hold report object
Dim lngTop As Long ' holds top value of control position
Dim lngLeft As Long ' holds left value of controls position
Dim title As String 'holds title of report
Dim lngBlack As Long
Dim rptData As String

lngBlack = RGB(0, 0, 0)

'set the title
title = "Activity Summary Report"

' initialise position variables
lngLeft = 0
lngTop = 0

'Create the report
Set rpt = CreateReport

' set properties of the Report
With rpt
.Width = 8500
.RecordSource = sSQL
.Caption = title
End With

sSQL = "SELECT Submission_Date, Nbr_Files_In_Set, Motion_filesize, Motion_DatePeriodFrom, Motion_DatePeriodTo " _
& "From dbo_Motion_Imagery;"

' Open SQL query as a recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(sSQL, dbOpenDynaset, dbSeeChanges)

rptData = "Motion Imagery"

' Create Report Header Title
Set lblNew = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Activity Summary Report", 0, 0)
With lblNew
.FontBold = True
.FontSize = 14
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
.SizeToFit
End With

' Create Report Header Sub Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , rptData, 0, 600)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
.SizeToFit
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Submission", 200, 1000)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
.SizeToFit
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Date", 600, 1300)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
.SizeToFit
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Classification", 1700, 1000)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
.SizeToFit
End With


' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Nbr of Files", 3400, 1000)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
.SizeToFit
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Filesize", 4900, 1000)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
.SizeToFit
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Date Period", 5950, 1000)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
.SizeToFit
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "Date Period", 7500, 1000)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
.SizeToFit
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "From", 6300, 1300)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
.SizeToFit
End With

' Create Report Column Header Title
Set lblSub = CreateReportControl(rpt.Name, acLabel, _
acPageHeader, , "To", 8000, 1300)
With lblSub
.FontBold = True
.FontSize = 12
.FontName = "Arial"
.ForeColor = ingBlack
.FontUnderline = Yes
.SizeToFit
End With

' Create Report Column Header Title
'Set lblSub = CreateReportControl(rpt.Name, acLabel, _
'acPageHeader, , "-", 0, 1500, 1500)
'With lblSub
' .BorderStyle = Solid
' .BorderWidth = 1
' .BorderColor = ingBlack
'End With

' Create corresponding label and text box controls for each field.
For Each fld In rs.Fields

'Create new text box control and size to fit data.
Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
acDetail, , fld.Name, lngLeft + 1500, lngTop)
txtNew.SizeToFit

' Create new label control and size to fit data.
Set lblNew = CreateReportControl(rpt.Name, acLabel, acDetail, _
txtNew.Name, fld.Name, lngLeft, lngTop, 1500, txtNew.Height)
lblNew.SizeToFit

' Increment top value for next control
lngTop = lngTop + txtNew.Height + 25
Next

' Create datestamp in Footer
Set lblNew = CreateReportControl(rpt.Name, acLabel, _
acPageFooter, , Now(), 0, 0)

' Create page numbering on footer
Set txtNew = CreateReportControl(rpt.Name, acTextBox, _
acPageFooter, , "='Page ' & [Page] & ' of ' & [Pages]", rpt.Width - 1000, 0)
txtNew.SizeToFit

' Open new report.
DoCmd.OpenReport rpt.Name, acViewPreview

Cleanup:
' Cleanup all objects - close and exit form/Report
'resume next on errors
On Error Resume Next

Set rs = Nothing
rs.Close
Set rpt = Nothing
Exit Sub

'Error Handler Routine
RunReport_ErrorHandler:
Select Case Err
Case Else
MsgBox Err & ": " & Err.Description
End Select

GoTo Cleanup

End Sub




Hi there,

I've got a macro to send a report as an email, this all works ok, however, I have the option to attach a file into my form/table/query and report and I would like to have this file included when you press the button to email the report. I can choose from the various report output formats and see an image of the attachment, e.g. an excel file in the report, however how can this file be opened by the recipient of the emailed report?

any ideas/suggestions greatly appreciated.

regards
Ben




Is it possible to send a report as an e-mail, not just attached to an e-mail? We use Outlook which is set up to use Word as the e-mail editor, so I imagine it's somehow possible.




I have several workstations in the office that fail, when trying to send a report as an email attachment, in .rtf format. Most of the workstations have no problem doing this, but on a couple machines we get the error, "can not find the database on the server". The procedure we use is to run the report, then click file, then "Send To", Mail Recipient (as Attachment), once we click this it starts to generate the output, then the error pops up. This problem does not occur when the report is output to any other format, only .rtf. It leads me to believe that maybe another application screwed up the .rtf format, but I don't know where to start looking for a fix. Any help would be greatly appreciated.




HI, am pretty new at this and don't have any VBA training but I am develoing a 2007 MS Access database for folks at work and could use a sanity check on what I am doing.

The dB will produce many reports based on different division and branch workload. At present to send a report as a PDF file I create a macro to Send Object from MS Access ribbon. I choose Report, select report name, choose PDF format and add description for the subject line of the mail window. The macro works fine and produces the PDF file of each record as I like. In doing so I'm up to over 50 macros that send out reports as PDF files.

Question: I assume their is a much easier way. What am I doing wrong ?

Thank you for your response ! Art




Ok, here comes a hard one...

At least measured by the amount of threads I have read without seeing a solution that is clear and succinct...

So, even if its not clear or succinct... Does anyone know how to Export a Report as:

a) a .pdf in full color and then convert to a jpeg from there. (using vba code)

b) a .jpeg in full color (more immediate than the above, again using vba code)

I would like to do this without using thirdparty software such as pdf995 or cutepdf...

I suspect I am looking for a "DoCmd.OutputTo" or similar code that would do the above.. preferrably option (b).

Thanks in advance!




If I use the sendobject macro command to send a report to a client, it appears that I have to type in the clients address in the To: field. What I want to do is design a macro that will look in a form to get the address rather than have to type it in every time. I am capable of creating a form that looks at the report and matches that to an address, which will then appear on the form; however, I can not get the address from the form to the To: field of the sendobj macro command. What else can I do.
Don




I have a financial report on various schools that I need to send to each school principal. Each school will receive its separate financial page out of that long report. So, how do I mass emailing the report out? I can break the report out into many and do the Macro SendObject, but it takes time because we have or 100 schools. By the way, we do have MS Outlook too. Any suggestions? Thanks!




I receive The OutputTo Action Was Cancelled, Error 2501 when trying to output a report as a pdf with Access 2010 and Adobe Acrobat X.

DoCmd.OutputTo acOutputReport, "Data", acFormatPDF, "FullPathToFile.pdf", True

Can anyone tell me what might be causing this. The report has data and I can preview it and even export to pdf by using the acrobat tab in the ribbon. Thanks!




Hi everyone, im posting this topic in the reports section because i think this is the right place for it. however it might need to be in the forms department. so if i posted this in the wrong section, my apologies in advance.

I've created a database for my church for collecting information on visitors so we can keep in touch with them. and the database is pretty much complete, however, im having trouble with trying to link multiple reports together. Sometimes our guest have different last names, but they are in the same family. so what i need to do is find a way to link the multiple entries together. So say if a Grandmother is visiting our church with her grandson who has a different last name. how can i link those 2 together? i wasnt able to really come up with any ideas on my own. so any suggestions are greatly appreciated.