Email Attachment (2003)

Topic

New Reply

Hi,

If I use automation to send email with attachment in access, can user select email address from the company global list instead default email address in the code? Below is the code provided by Hans:

Function SendMail( _
Recipient As String, _
Subject As String, _
Message As String, _
Attachment As String) As Boolean

Dim objOL As Outlook.Application
Dim objMI As Outlook.MailItem
Dim blnNotActive As Boolean
Dim intPos1 As Integer, intPos2 As Integer

SysCmd acSysCmdSetStatus, “A moment please. Your e-mail message is being sent.”
DoCmd.Hourglass True

‘ Check whether Outlook is active
On Error Resume Next
Set objOL = GetObject(, “Outlook.Application”)
blnNotActive = (Err 0)

If blnNotActive Then
‘ If not, we start Outlook
Err.Clear
Set objOL = CreateObject(“Outlook.Application”)
End If

On Error GoTo Err_Mail

‘ Create e-mail message
Set objMI = objOL.CreateItem(olMailItem)
With objMI
intPos2 = 1
intPos1 = InStr(intPos2, Recipient, “;”)
Do While intPos1 > 0
.Recipients.Add Mid$(Recipient, intPos2, intPos1 – intPos2)
intPos2 = intPos1 + 1
intPos1 = InStr(intPos2, Recipient, “;”)
Loop
.Recipients.Add Mid$(Recipient, intPos2)
.Subject = Subject
.Body = Message
.Attachments.Add Attachment, olByValue
.Send
End With
SendMail = True

Exit_Mail:
‘ Release object memory
On Error Resume Next
If Not (objMI Is Nothing) Then objMI.Close olDiscard
Set objMI = Nothing
If blnNotActive And Not (objOL Is Nothing) Then objOL.Quit
Set objOL = Nothing
SysCmd acSysCmdClearStatus
DoCmd.Hourglass False
Exit Function

Err_Mail:
SendMail = False
Resume Exit_Mail
End Function

Thanks

Regards

Thanks

Reply | Quote

Replies

Remove the part that sets the recipient(s) and change the line

.Send

to

.Display

The e-mail will be displayed on screen, and the user can enter the recipients, then send it.

Reply | Quote

Hi Hans,

Thanks! I added a reference to Outlook library. But I don

Reply | Quote

This function should be copied into a standard module (the kind you create by clicking New in the Modules section of the database window). You can call it – for example – in the On Click event procedure of a command button on a form:

Private Sub cmdSendMail_Click()
Dim strFile As String
strFile = “C:DocsReport.pdf”
‘ Code to export report to PDF file goes here (its name is in strFile)
…
‘ Send e-mail
Call Function SendMail(“you@somewehere.com”, “Report”, “See the attached report”, strFile)
End Sub

Reply | Quote

Hi Hans,

Can you help me to look following error message? I don’t know why call funcation is not wok?

Thanks

Reply | Quote

You should use Call SendMail(…), not Call Function SendMail(…)

Please don’t post pictures larger than 640 (wide) by 480 (high)

Reply | Quote

Hi Hans,

I changed to Call SendMail(…), but still error message pops up. Do I have to change Call SendMail(…) to the following code too?

Function SendMail( _
Recipient As String, _
Subject As String, _
Message As String, _
Attachment As String) As Boolean

Dim objOL As Outlook.Application
Dim objMI As Outlook.MailItem
Dim blnNotActive As Boolean
Dim intPos1 As Integer, intPos2 As Integer

SysCmd acSysCmdSetStatus, “A moment please. Your e-mail message is being sent.”
DoCmd.Hourglass True

‘ Check whether Outlook is active
On Error Resume Next
Set objOL = GetObject(, “Outlook.Application”)
blnNotActive = (Err 0)

If blnNotActive Then
‘ If not, we start Outlook
Err.Clear
Set objOL = CreateObject(“Outlook.Application”)
End If

On Error GoTo Err_Mail

‘ Create e-mail message
Set objMI = objOL.CreateItem(olMailItem)
With objMI
intPos2 = 1
intPos1 = InStr(intPos2, Recipient, “;”)
Do While intPos1 > 0
.Recipients.Add Mid$(Recipient, intPos2, intPos1 – intPos2)
intPos2 = intPos1 + 1
intPos1 = InStr(intPos2, Recipient, “;”)
Loop
.Recipients.Add Mid$(Recipient, intPos2)
.Subject = Subject
.Body = Message
.Attachments.Add Attachment, olByValue
.Send
End With
SendMail = True

Exit_Mail:
‘ Release object memory
On Error Resume Next
If Not (objMI Is Nothing) Then objMI.Close olDiscard
Set objMI = Nothing
If blnNotActive And Not (objOL Is Nothing) Then objOL.Quit
Set objOL = Nothing
SysCmd acSysCmdClearStatus
DoCmd.Hourglass False
Exit Function

Err_Mail:
SendMail = False
Resume Exit_Mail
End Function

Thanks

Reply | Quote

Apparently you have a control named SendMail and a function named SendMail. This is confusing. You must rename one of them.

Reply | Quote

Hi Hans,

You are correct. After I changed the control name, there’s no error message. But nothing appears too. I didn’t see any acitivies. What should I do now?

Thanks

Regards

Reply | Quote

Is the email being sent? You have the command .Send in there. If you want to see something you should use .Display.

Reply | Quote

Hi Hans,

User need to select email address from our company’s global list, so I removed the part that sets the recipient(s) and change the line “.Send” to “.Display” as you told me. Please see below currect code after I changed:

‘ Create e-mail message
Set objMI = objOL.CreateItem(olMailItem)
With objMI

.Subject = Subject
.Body = Message
.Attachments.Add Attachment, olByValue
.Send
End With
SendMail = True

Below is the code of Command button.

Private Sub Email_Click()
Dim strFile As String
strFile = “D:PDFTest.pdf”
‘ Code to export report to PDF file goes here (its name is in strFile)
‘ Send e-mail

Call SendMail(“”, “Report”, “See the attached report”, strFile)
End Sub

I clicked this Command button, the following message pops up, I clicked “Yes”. Nothing appears. I did check Outlook, no any email was sent.

I really have no idea what’s going on. Please help…. Thanks a lot.

Regards

Reply | Quote

The warning you see is caused by Outlook. It proves that your code *does* call Outlook.
You can download and install the free utility ClickYes to suppress the warning.

Your code still has .Send instead of .Display.

If your code really is

Private Sub Email_Click()
Dim strFile As String
strFile = “D:PDFTest.pdf”
‘ Code to export report to PDF file goes here (its name is in strFile)
‘ Send e-mail

Call SendMail(“”, “Report”, “See the attached report”, strFile)
End Sub

it doesn’t create a file. You were supposed to add code below the line

‘ Code to export report to PDF file goes here (its name is in strFile)

yourself.

Reply | Quote

Hi Hans,

Sorry for keeping ask you for help. Below is code that I added export file:

Private Sub Search_MRN_Click()

Dim strFile As String
strFile = “C:Program FilesAdobeAcrobat 4.0PDF OutputTest.pdf”
‘ Code to export report to PDF file goes here (its name is in strFile)

Dim strDefaultPrinter As String

‘Get the default printer being used
strDefaultPrinter = Application.Printer.DeviceName

‘ Switch the Default Printer to print to Adobe
Set Application.Printer = Application.Printers(“Acrobat Distiller”)

‘Create the PDF File / Print to PDF
DoCmd.OpenReport “Test”

‘Reset the printer to the original default printer
Set Application.Printer = Application.Printers(strDefaultPrinter)

Call SendMail(“”, “Report”, “See the attached report”, strFile)

End Sub

I changed “.Send” to “.Display”, please see below:

‘ Create e-mail message
Set objMI = objOL.CreateItem(olMailItem)
With objMI

.Subject = Subject
.Body = Message
.Attachments.Add Attachment, olByValue
.Display
End With
SendMail = True

After I clicked command button, the report in PDF format is open. But no email appears. Can you help me to look is any code wrong?

Thanks

Regards

Reply | Quote

Click in the first line of the SendMail function, ie. the line Function SendMail(…) As Boolean.
Press F9 to set a breakpoint.
Switch to the form and click the button.
The code should pause at the first line of the SendMail function.
You can now press F8 to single-step through the function (each time you press F8, you execute one statement).
Try to find out what happens – does the code suddenly jump to another line, for example?

Reply | Quote

Image reduced in size by HansV

Hi,

Thank you for help again. I try to put the code you provide to me in the command button. But the following error message pops up:

Reply | Quote

Gary,
First, you don’t have any code to export the report! Where the three … are, you need to replace them with your export code. Second, you have to put an email address in there. See the thread starting at post 211,036 for help.

Reply | Quote

Thanks.

But I need to use network global list to select name, so I don’t have anybody’s email address yet. The problem I have is Access don’t call funcaiton.

I need help, please.

Regards

Reply | Quote