Send email from Access using a button or command button

Question

Hi I have been working on the following code, but getting error message.  I would also like to add fields from my form to insert in the body of the message.  Also I want to send with from a command button.  Any suggestions?

Private Sub SendMail()

Dim .App As Outlook.Application

Dim .Mail As MailItem

Set .App = CreateObject("Outlook.application")

Set .Mail = oApp.CreateItem(olMailItem)

.Mail.Body = "Body of the email"

.Mail.Subject = "Test Subject"

.Mail.To = "test@example.com"

.Mail.Send 'this sends the mail

Set .Mail = Nothing

Set .App = Nothing

End Sub

***Post moved by the moderator to the appropriate forum category.***

Answer 1

You'd build your body text to concatenate in your control values

"Mr. " &  Me.[OwnerName] & ", beginning today " & Format("yyyy-mmm-dd", Date())

As for bold, bullets and everything else, you need to use html tags.  Basically you need to build the html code, just like a webpage.

Answer 2

DoCmd.SendObject acSendNoObject, , , "ToSomeone @ somewhere.com", , , "Subject", "E-mail Body", False

would become

DoCmd.SendObject acSendNoObject, , , Me.RecipientEmailControlName, , , Me.SubjectControlName, Me.EmailBodyControlName, False

RecipientEmailControlName -> Name of the control on your form which houses the e-mail address to send the e-mail to

SubjectControlName -> Name of the control on your form which houses the subject to give to the e-mail

EmailBodyControlName -> Name of the control on your form which houses the text to be used to populate e-mail body

Answer 3

DoCmd.SendObject acSendNoObject, , , "ToSomeone @ somewhere.com", , , "Subject", "E-mail Body", False

Answer 4

If you're just sending a text message, no attachements, then you're better off using DoCmd.SendObject.

If you want to stick with Outlook automation then you should use a reusable function such as: http://www.devhut.net/2010/09/03/vba-send-html-emails-using-outlook-automation/

You can use the above example to troubleshoot your code. 

Have you set a reference to the Outlook Library?  One way or another, I'd use Late Binding instead, thus no references required and your code would then turn into

Public Sub SendMail()

    Dim App                   As Object    'Outlook.Application

    Dim Mail                  As Object    'Outlook.MailItem

    Dim MailRecip             As Object    'Outlook.Recipient

    Const olMailItem = 0

    Set App = CreateObject("Outlook.application")

    Set Mail = oApp.CreateItem(olMailItem)

    With Mail

        Set MailRecip = .Recipients.Add("*** Email address is removed for privacy ***")

        MailRecip.Type = 1    'Designates the above is TO recipients

        .Subject = "Test Subject"

        .Body = "Body of the email"

        For Each MailRecip In .Recipients

            If Not MailRecip.Resolve Then

                Mail.Display

            End If

        Next

        .Send    'this sends the mail

    End With

    Set MailRecip = Nothing

    Set Mail = Nothing

    Set App = Nothing

End Sub

Answer 5

What would the code look like if using DoCmd.SendObject.?