Automatically Generate and Send an E-mail

[LadyDi]

I have previously used the docmd.SendObject command to generate an e-mail in an Access database. My manager just came to me and asked if I could program a button on the database to generate and send an e-mail without the associate needing to do anything (like click the Send button on the e-mail) Is this possible? If so, what do I need to do to accomplish it? Can I still use the docmd.SendObject command, or is there a better way?

Any assistance you can provide would be greatly appreciated.

 

[michaeljryan78]

You would need to create a reference to Microsofr Office Object. Mine in Microsoft Office 14.0 Object Library. Here is some sample code:

<code>

Sub Outlook_SendMail()
Outlook_OpenOutlook 'this is a sub further down the page
Dim mailOutlook As Outlook.MailItem
Dim strDoc As String
`strDoc = CurrentProject.Path & "\sample.docx"
`if you wish to add an attachment
Set mailOutlook = appOutlook.CreateItem(olMailItem)
With mailOutlook
.Subject = "Sample email" 'place subject here
.To = user@website.com
.Body = "Sample Body." ' place text here
'.Attachments.Add strDoc 'attach the doc
' display the email​

.Display
'send the email
.send
End with
Set MailOutlook = Nothing
End sub

Sub Outlook_OpenOutlook()
' Initialize outlook objects
On Error Resume Next
Set appOutlook = GetObject(, "Outlook.Application")
If Err <> 0 Then
' attempt to start outlook
' Note that this code can be used to start a second instance of outlook
Set appOutlook = New Outlook.Application
Set namespaceOutlook = appOutlook.GetNamespace("MAPI")
Dim folderOutlook As Folder
Set folderOutlook = namespaceOutlook.GetDefaultFolder(olFolderInbox)
' make outlook visible on the desktop
folderOutlook.Display
Else
Set namespaceOutlook = appOutlook.GetNamespace("MAPI")
End If
End Sub

<\Code)

 

[LadyDi]

Thank you very much for the code, I appreciate it. I just have a quick question. Does this need to be saved as a module and then get called from a button on my form? If so, what is the proper way to call it?

 

[michaeljryan78]

You can paste this right in the Forms module.

for the button:

Sub cmdSample OnClick()
Sendmail
End sub

 

[michaeljryan78]

If this was helpful, click on the sacel to add to my rep!

 

[LadyDi]

Okay, I added this code to my form and then I added "SendMail" to my button. However, when I click on my button, I keep getting an error message stating "Object Variable or With block variable not set". When I click on Debug, it highlights the line of code for the subject line (.Subject = "Sample Email"). What do I need to do to correct this?

 

[michaeljryan78]

sorry, the code for the OnClick should be

Outlook_SendMail

place a breakpoint at the beginning of "Outlook_SendMail" sub by highlighting the begiining line and press F9. Press F8 to walk through the code. should be fine. I just tested it myself.

 

[LadyDi]

I made the changes you suggested and am still having problems. I even recopied all of the code. The only part that I changed so far, was to change the e-mail address from user@website.com to my e-mail address, and I enclosed that in quotation marks. I added Microsoft Outlook 12.0 Object Library as a reference (I didn't have the option of 14.0). When I inserted the breaking point and then tried to run through it, I still received the same message listed below and a message stating that an "object is required". This message appeared on the line showing Set mailOutlook = appOutlook.CreateItem(olMailItem). Incidentally, I don't need attachments on this e-mail, just the message - if that makes a difference. Is there something I can do to get the Outlook 14.0 object library. Would that make a difference? What do you suggest? Below is the code that I have right now.

Sub Outlook_SendMail()
Outlook_OpenOutlook 'this is a sub further down the page
Dim mailOutlook As Outlook.MailItem
Dim strDoc As String
'strDoc = CurrentProject.Path & "\sample.docx"
'if you wish to add an attachment
Set mailOutlook = appOutlook.CreateItem(olMailItem)
With mailOutlook
.Subject = "Sample email" 'place subject here
.To = "diane@rr.com"
.Body = "Sample Body." ' place text here
'.Attachments.Add strDoc 'attach the doc
' display the email
.Display
'send the email
.send
End With
Set mailOutlook = Nothing
End Sub
Sub Outlook_OpenOutlook()
' Initialize outlook objects
On Error Resume Next
Set appOutlook = GetObject(, "Outlook.Application")
If Err <> 0 Then
' attempt to start outlook
' Note that this code can be used to start a second instance of outlook
Set appOutlook = New Outlook.Application
Set namespaceOutlook = appOutlook.GetNamespace("MAPI")
Dim folderOutlook As Folder
Set folderOutlook = namespaceOutlook.GetDefaultFolder(olFolderInbox)
' make outlook visible on the desktop
folderOutlook.Display
Else
Set namespaceOutlook = appOutlook.GetNamespace("MAPI")
End If
End Sub
Private Sub Command212_Click()
Outlook_SendMail
End Sub

 

[michaeljryan78]

did it run through the sub Outlook_OpenOutlook?

 

[michaeljryan78]

sorry,
Dim appOutlook As Outlook.Application
Dim namespaceOutlook As Outlook.Namespace
right after option explicit at the top of the forms module.

 

[LadyDi]

That worked great. However, before it would send the e-mail, I got a warrning message from Outlook (please see attached). Is there a way to get rid of that message?

 

[michaeljryan78]

THere is a setting in outlook to supress that message in the Outlook Options > Trust center > Trust Center Settings > Programmic Access > "Never Warn me About..blah, blah

 

[LadyDi]

That is perfect. Thank you so much.

 

[michaeljryan78]

No Problem!

 

[LadyDi]

I have added this code to another database that I am building and I have a quick question. My new database is going to generate e-mails to our service technicians notifying them when an order they placed gets put on backorder. This database will actually be generating about twenty e-mails a week. My manager just told me that he would like the database to use a generic e-mail account to send the e-mails instead of the e-mail account of the person running the database. That way, one person won't get bombarded with e-mails from the field. Is there a way to get this code to send the e-mails from an account other than the default on the computer? In other words, in my Outlook, I have two accounts. One is the default, where the e-mail address contains my name. The other account in my Outlook is a generic, department account. The e-mail for that account contains the department name instead of mine. I would like to use that account when sending these e-mails. Is that possible?