Sending email using VBA code (1 Viewer)

[Sunnray]

Sending email using VBA code - RESOLVED

I wish to send one email to txtFillOutBy via her txtEmail using Outlook.

Can anyone let me know if this will work in 2007? Current errors are undefined datatypes and data member not found. They're in red.

This is the third block of code I've found on the internet, and the simplest yet there is still pieces I don't understand. Like, at this point do I still need rst? The original code had a loop for a mass-mailing. I removed everything I could like variables for the subject and body.

Private Sub btnNotify_Click()
Dim mess_body As String
Dim rst As DAO.Recordset
Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Set rst = Me.RecordsetClone
If IsNull(Me.txtEmail) Then
MsgBox "skipping " & Me.txtLastName & " no email address."
GoTo skip_email
End If
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
With MailOutLook
.To = txtEmail
.Subject = "[ACTION] " & cmbActionType.Value & " for " & txtFirstName & " " & txtLastName
.Body = "Dear " & txtFillOutBy & "," & vbCrLf & vbCrLf & cmbActionType.Value & " for " & txtFirstName & " " & txtLastName
If left(Me.Mail_Attachment_Path, 1) <> "<" Then
.Attachments.Add (Me.Mail_Attachment_Path)
End If
'next line would let MS Outlook API send the note
'without storing it in your sent bin
'.DeleteAfterSubmit = True
.Send
End With
skip_email:
rst.Close
Set rst = Nothing
End Sub

 

[iworkonline]

Hi

Have you seen the code here? I hope that helps for you. I have not tried it in the access2007

 

[Sunnray]

Hi

Have you seen the code here? I hope that helps for you. I have not tried it in the access2007

Your link requires VBA code added to Outlook first. While I'm not concerned with the warning I don't have the time to be playing with Outlook.

 

[boblarson]

Change these two lines:

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem


to this:

Dim appOutLook As Object
Dim MailOutLook As Object

That should get you using late binding so you won't need to set a reference in the references and then makes it so that it isn't version dependent.

 

[Sunnray]

Re: Sending email using VBA code, WORKS

Change these two lines:

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem


to this:

Dim appOutLook As Object
Dim MailOutLook As Object

That should get you using late binding so you won't need to set a reference in the references and then makes it so that it isn't version dependent.

It worked. Great. Thanks Bob.

 

[Sunnray]

For anyone looking at this code I commented out the attachment part as I don't need it.

Private Sub btnNotify_Click()
    Dim mess_body As String
    Dim rst As DAO.Recordset
    Dim appOutLook As Object
    Dim MailOutLook As Object
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
    Set rst = Me.RecordsetClone
    If IsNull(Me.txtEmail) Then
        MsgBox "No email address for " & txtFillOutBy & "."
        GoTo skip_email
    End If
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(olMailItem)
    With MailOutLook
        .To = txtEmail
        .Subject = "[ACTION] " & cmbActionType.Value & " for " & txtFirstName & " " & txtLastName
        .Body = "Dear " & txtFillOutBy & "," & vbCrLf & vbCrLf & _
                cmbActionType.Value & " for " & txtFirstName & " " & txtLastName
        .Send
    End With
skip_email:
    rst.Close
    Set rst = Nothing
End Sub