By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,466 Members | 1,141 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,466 IT Pros & Developers. It's quick & easy.

VBA code for Access to email PDF Attachment through LotusNotes

P: 2
I have an Access database where I want to send a PDF file as an attachment. I have code that sends the basic email from Access through LotusNotes and it works well. What I would like help on is:
Does LotusNotes need to be configured any certain way to handle the attachment?
I also need help with the VBA code to attach the PDF file to the email. The PDF name is Report1 and will be stored here C:\Temp\Report1.pdf Each time a new file is created, it will have the same name and will be overwritten each time. If code is need to allow time for the PDF to be written, help with the code would be nice.
I am using a table to store the To:, CC:, Email address:, etc. The queries in code delete the table or append new data as needed. Please reply if you need further clarification on this part of my code.
I have tried several pieces of code found on websites, but I'm not doing something right......I need someone's help with this.

I'm new to writing code in VBA, so any help will be greatly appreciated.

My code is below and works well for what I want to do, except for attachments. If anyone can help. please modify my code.

Private Sub MGR_Send_Email_Click()

Dim defPrinter As String, NewPrinter As Printer

'Get the default printer name
defPrinter = Application.Printer.DeviceName

'Create a new printer object
Set NewPrinter = Application.Printers("CutePDF Writer") 'use your printer name here

'Set the default printer to the new printer
Set Application.Printer = NewPrinter

'Open the report
DoCmd.OpenReport "Report1", acViewNormal 'use your actual report name here

'Print the report (the next line seems to print the report and the form so I removed it, if your report doesn't print then remove the apostrophe on the next line)
'DoCmd.PrintOut

'Close the report
DoCmd.Close acReport, "Report1", acSaveNo 'report name again!

'Reset the printer back to the default printer
Set NewPrinter = Application.Printers(defPrinter)
Set Application.Printer = NewPrinter


Dim stDocName As String
Dim stDocName1 As String
Dim stDocName2 As String
Dim stDocName3 As String
Dim stDocName4 As String

stDocName = " MRG_Temp_Email App qry"
DoCmd.OpenQuery stDocName, acNormal, acEdit

stDocName1 = " MGR_Temp_Auto_Email App qry"
DoCmd.OpenQuery stDocName1, acNormal, acEdit

If MsgBox("Ready to Automatically Send Email?", vbOKCancel, "Confirm") = vbOK Then

Dim db As DAO.Database
Dim rstAutoEmail As DAO.Recordset
Dim strTo As String
Dim strCC As String
Dim strBCC As String
Dim strSubject As String
Dim strBody As String

Set db = CurrentDb()

Set rstAutoEmail = db.OpenRecordset("MGR_Temp_AutoEmail", dbOpenDynaset)

Do Until rstAutoEmail.EOF
strTo = rstAutoEmail![To email]
strCC = ""
If Not IsNull(rstAutoEmail![CC email]) Then strCC = rstAutoEmail![CC email]
strBCC = ""
If Not IsNull(rstAutoEmail![BCC email]) Then strBCC = rstAutoEmail![BCC email]
strBody = rstAutoEmail!Body
strSubject = rstAutoEmail![Subject]

If MsgBox("Send This Email to " & strTo & "?", vbOKCancel, "Confirm") = vbOK Then
SendMail strTo, strCC, strBCC, strSubject, strBody, ""

End If

rstAutoEmail.MoveNext
Loop

rstAutoEmail.Close
db.Close
End If

On Error GoTo Err_MGR_Send_Email_Click

stDocName2 = "MGR Review qry"
DoCmd.OpenQuery stDocName2, acNormal, acEdit

stDocName3 = "MGR_Temp_Email Del qry"
DoCmd.OpenQuery stDocName3, acNormal, acEdit

stDocName4 = "MGR_Temp_Auto_Email Del qry"
DoCmd.OpenQuery stDocName4, acNormal, acEdit


Exit_MGR_Send_Email_Click:
Exit Sub

Err_MGR_Send_Email_Click:
MsgBox Err.Description
Resume Exit_MGR_Send_Email_Click

End Sub
Jul 6 '10 #1
Share this Question
Share on Google+
2 Replies


maxamis4
Expert 100+
P: 295
I don't have that much experience in Lotus but here is a link from another bytes article that will give you an idea.

http://bytes.com/topic/access/answer...ted-access-vba
Jul 7 '10 #2

P: 2
@maxamis4
Thanks....I'll take a look at it.
Jul 7 '10 #3

Post your reply

Sign in to post your reply or Sign up for a free account.