Email from MS Access form

Question

I have a button with an event procedure that updates a number of fields on my form and it works fine until I try to add the code to send an email.

I found the code here and modified it for my field names. I don't get any errors, it just doesn't send the email. I am using Office 365 and Windows 10.

I would also love the ability to review the email before it is sent, but haven't found anything on how to do that. Any assistance would be greatly appreciated! :)

Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem

Set oEmail = oApp.CreateItem(olMailItem)

oEmail.To = Me.EmailRule
oEmail.Subject = Me.Subject.Value
oEmail.Body = Me.txtBody.Value
If Len(Me.Attachment) > 0 Then
    oEmail.Attachments.Add Me.Attachment.Value
End If
With oEmail
    If Not IsNull(.To) And Not IsNull(.Subject) And Not IsNull(.Body) Then
        MsgBox "Email Sent!"
    Else
        MsgBox "please fill out the required fields."
    End If
End With

Did June7 answer solved your problem or still need help? Let us know your feedback. — Harun24hr, Feb 25 '21 at 02:55
Is Attachment an attachment type field with embedded file? Cannot directly add file from attachment type field. Must first export to folder location then attach from there. — June7, Feb 25 '21 at 19:21

June7 · Answer 1 · 2021-02-25T23:37:43.563

0

Need oEmail.Send or oEmail.Display if you prefer to review.

Testing oEmail.To/.Body/.Subject for Null will fail. These are never Null - they hold empty string until other data provided. Can verify with Debug.Print IsNull(oEmail.To) before populating. Trying to populate with Null will get 'Invalid use of Null' error. Handle possible Null with Nz: oEmail.To = Nz(Me.EmailRule, ""). Then test for empty string in oEmail elements:
If .To = "" Or .Subject = "" Or .Body = "" Then

Or test controls on form: IsNull(Me.EmailRule) and do it before trying to populate email elements. Consider:

Dim oApp As Outlook.Application
Dim oEmail As Outlook.MailItem
If IsNull(Me.EmailRule) Or IsNull(Me.Subject) Or IsNull(Me.txtBody) Then
    MsgBox "please fill out the required fields."
Else
    Set oEmail = oApp.CreateItem(olMailItem)
    With oEmail
        .To = Me.EmailRule
        .Subject = Me.Subject
        .Body = Me.txtBody
        If Len(Me.Attachment) > 0 Then
            .Attachments.Add Me.Attachment
        End If
        .Display
    End With
End If

Not related but might be of interest: Don't need to type .Value because that is default property for data controls.

edited Feb 25 '21 at 23:37

answered Feb 24 '21 at 22:35

June7

19,874
8
24
34

`.Display` will show the mail for review then need to send it. If OP wants to send mail directly without any warning then he must set [Programmatic Access](https://learn.microsoft.com/en-us/outlook/troubleshoot/security/a-program-is-trying-to-send-an-email-message-on-your-behalf) to `Never Warn.....` – Harun24hr Feb 25 '21 at 02:01
I don't have that option set and I don't get warning as long as MS Windows virus protection is up to date. In fact, option is greyed out and I cannot select. However, I am using Office 2010, maybe later versions act different. – June7 Feb 25 '21 at 02:08
Then that is fine but I get warning when try to send directly by `.send` to my office computers although antivirus and windows defender is always up to date. So, when I set it to `Never warn...` then I do not get any warning. – Harun24hr Feb 25 '21 at 02:13
Well, if I could set it I would. Haven't figured out how to 'un-grey' it. – June7 Feb 25 '21 at 02:30
You must login by `Administrator` user to computer to change settings. – Harun24hr Feb 25 '21 at 02:33
Ah, okay, did not think of that because other Option settings are available for changing. Although, this is a laptop, not a network connected PC. Never have quite figured out the 'Administrator' privileges. And if I were on network PC, IT prevents users having Administrator privileges. – June7 Feb 25 '21 at 02:52
Yes. Then it must be done by system administrator. – Harun24hr Feb 25 '21 at 08:17
I'm going to try it again today after work. I was at my limit when I made the changes June7 suggested, I started getting an error on the first line of code...errors I didn't previously get. :( – Tammy_T Feb 25 '21 at 13:11
So I modified to the below and i get an error at "Set oEmail =" line : Dim oApp As New Outlook.Application Dim oEmail As Outlook.MailItem If IsNull(Me.EmailRule) Or IsNull(Me.Subject) Or IsNull(Me.txtbody) Then MsgBox "please fill out the required fields." Else Set oEmail = oApp.CreateItem(olMailItem) With oEmail .To = Me.EmailRule .CC = "Anna" .Subject = Me.Subject .Body = Me.txtbody If Len(Me.attachment) > 0 Then .Attachments.Add Me.attachment End If .Display End With End If – Tammy_T Feb 25 '21 at 23:05
The error that i get is Run-time -2146959355 (80080005) Server execution failed – Tammy_T Feb 25 '21 at 23:08
Remove `New` qualifier. – June7 Feb 25 '21 at 23:34
Never mind, just tested and `New` makes no difference. No idea why your code fails. Google that error. – June7 Feb 25 '21 at 23:41
I finally had time and got this to work. I had to reinstall office. Now I found this code on here and want to incorporate into my code. https://stackoverflow.com/questions/8994116/how-to-add-default-signature-in-outlook I added the function as written, and the below is my code for the button, but i think i'm missing something. It creates the email but doesn't add the signature. – Tammy_T Mar 09 '21 at 00:42
Dim oApp As New Outlook.Application Dim oEmail As Outlook.MailItem If IsNull(Me.txtTo) Or IsNull(Me.txtSubject) Or IsNull(Me.txtbody) Then MsgBox "please fill out the required fields." Else Set oEmail = oApp.CreateItem(olMailItem) oEmail.BodyFormat = olFormatHTML With oEmail .To = Me.txtTo .cc = "ABC@xyz.com" .Subject = Me.txtSubject .HTMLBody = Me.txtbody & Signature .Attachments.Add "C:\Users\tt\Documents\CURRENT MONTH\Drafts\" & [txtSubject] & ".pdf" .Display End With End If – Tammy_T Mar 09 '21 at 00:42
You have a signature setup to be included in every new email by default? Need to modify code as shown in the answer in link you referenced. .Display must be executed before setting signature variable. This is really a new topic and should start a new question. Your original question has be answered. – June7 Mar 09 '21 at 01:03

Email from MS Access form

1 Answers1