Sending an email

[Elmobram22]

Hi all,

I have changed some VBA code for an email within access through outlook. I have used this code succesfully before and it is tied to buttons within my database without an issue. The problem I have now though is the HTMLbody section is coming back with -1 rather than the information it should. The email is populated from data within a form and the code looks like this...

Private Sub Command28_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sSql As String
Dim sFirst As String
Dim sLast As String
Dim sEmail As String

sSql = "SELECT FirstName, LastName , email, LeftAV "
sSql = sSql & "FROM tblStaff "
sSql = sSql & "WHERE [LeftAV] = no;"

Set db = CurrentDb
Set rst = db.OpenRecordset(sSql, dbOpenSnapshot)
If rst.RecordCount > 0 Then
rst.MoveFirst
Do While Not rst.EOF
sFirst = rst.Fields("FirstName")
sLast = rst.Fields("LastName")
sEmail = rst.Fields("email")

Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItem(olMailItem)
Dim strPath As String

strPath = "\\25.45.65.15\k\logo.jpg"

If strPath <> "" Then

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

With MailOutLook
.To = sEmail
.Subject = "Meeting"
.HTMLBody = "Hi " & [sFirst] & "," & "<br>" & "<br>" _
& "The date of the next " & [Combo4].Column(1) & " at " & [Combo0].Column(1) & " is at " & [pbTime] & " on " & [pbDate] < br > " & " < br > "" _
& "All are welcome to attend. Those who work regular shifts at the home will be paid for their time." < br > " & " < br > "" _
& "Please contact the Team Leader to let them know if you will be attending. Thank you." < br > " & " < br > "" _
& "Kind regards," & "<br>" & "<br>" _
& "Our Place Ltd" & "<br>" & "<br>"
.Display
End With
Else
MsgBox "No file matching " & strPath & " found. Please make sure the file is complete." & vbCrLf & _
"Processing terminated."
Exit Sub
End If


rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
MsgBox "Emails Sent"
End Sub

 

[isladogs]

Sounds like one or more of your variables isn't populated
Try doing debug.print on the .htmlbody section

If that's also -1 (true) then add a breakpoint on that section and check what each variable contains

 

[Orthodox Dave]

Hi,

You haven't declared variables myOlApp, myItem, appOutLook, MailOutLook

Dim myOlApp As Outlook.Application
Dim myItem as Outlook.MailItem

etc etc.

Hope that helps.

 

[Elmobram22]

Private Sub Command28_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim sSql As String
Dim sFirst As String
Dim sLast As String
Dim sEmail As String

sSql = "SELECT FirstName, LastName , email, LeftAV "
sSql = sSql & "FROM tblStaff "
sSql = sSql & "WHERE [LeftAV] = no;"

Set db = CurrentDb
Set rst = db.OpenRecordset(sSql, dbOpenSnapshot)
If rst.RecordCount > 0 Then
rst.MoveFirst
Do While Not rst.EOF
sFirst = rst.Fields("FirstName")
sLast = rst.Fields("LastName")
sEmail = rst.Fields("email")

Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItem(olMailItem)
Dim strPath As String

strPath = "\\25.45.61.5\logo.jpg"

If strPath <> "" Then

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

With MailOutLook
.To = sEmail
.Subject = [Combo4].Column(1) & " at " & [Combo0].Column(1) & " on " & [pbDate] & " at " & [pbTime]
.HTMLBody = "Above is the date of the next meeting." < br > " & " < br > "" _
& "All are welcome to attend. Those who work regular shifts at the home will be paid for their time." < br > " & " < br > "" _
& "Please contact the Team Leader to let them know if you will be attending. Thank you." < br > " & " < br > "" _
& "Kind regards," & "<br>" & "<br>" _
& "Our Place Ltd" & "<br>" & "<br>"
.Display
End With
Else
MsgBox "No file matching " & strPath & " found. Please make sure the file is complete." & vbCrLf & _
"Processing terminated."
Exit Sub
End If


rst.MoveNext
Loop
End If
rst.Close
Set rst = Nothing
MsgBox "Emails Sent"
End Sub

I have changed it to this and the subject is populating correctly but the .htmlbody is still showing as -1. I'm lost.

 

[Orthodox Dave]

I see you still haven't declared variables for myOlApp, myItem, appOutLook, MailOutLook as I suggested and this might be the cause.

To find out, I suggest you create a string variable for the HTML body first and see if that is populating, before inserting it into the email. I tend to use MsgBox rather than Debug.Print, but whatever your preference:

Dim BodyHTML as string
BodyHTML = "Above is the date of the next meeting." < br > " & " < br > "" _
& "All are welcome to attend. Those who work regular shifts at the home will be paid for their time." < br > " & " < br > "" _
& "Please contact the Team Leader to let them know if you will be attending. Thank you." < br > " & " < br > "" _
& "Kind regards," & "<br>" & "<br>" _
& "Our Place Ltd" & "<br>" & "<br>"

MsgBox(BodyHTML)

.HTMLBody = BodyHTML

Run the code and see what your message box says. I doubt it will be "-1". If it giving what you want, then the problem is with the email section. I then suggest you start by declaring the variables.

 

[Elmobram22]

Here are how the form and email are looking...

 

[Elmobram22]

I see you still haven't declared variables for myOlApp, myItem, appOutLook, MailOutLook as I suggested and this might be the cause.

To find out, I suggest you create a string variable for the HTML body first and see if that is populating, before inserting it into the email. I tend to use MsgBox rather than Debug.Print, but whatever your preference:

Dim BodyHTML as string
BodyHTML = "Above is the date of the next meeting." < br > " & " < br > "" _
& "All are welcome to attend. Those who work regular shifts at the home will be paid for their time." < br > " & " < br > "" _
& "Please contact the Team Leader to let them know if you will be attending. Thank you." < br > " & " < br > "" _
& "Kind regards," & "<br>" & "<br>" _
& "Our Place Ltd" & "<br>" & "<br>"

MsgBox(BodyHTML)

.HTMLBody = BodyHTML

Run the code and see what your message box says. I doubt it will be "-1". If it giving what you want, then the problem is with the email section. I then suggest you start by declaring the variables.

Yeah I'm not sure what I need to do to determine variables for it. Like I said it works exactly in the other email varients. Just not this one and now it has no variables in the body anyway.

 

[Orthodox Dave]

I see how frustrating this is for you.

Have you tried creating the HTML body as a string (see my last post) to see if this creates the string you wanted in your email?

As regards the variable declaration, I've had another look. You seem to have set the Outlook variables twice:

Set myOlApp = CreateObject("Outlook.Application")
Set myItem = myOlApp.CreateItem(olMailItem)
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

But in your email code, you have only usee the third and fourth, as evidenced by your "With MailOutLook" line. You don't refer again to "myItem", so I think you could remove the first 2 lines above (or comment them out).

Also you should declare these as variables with a Dim statement. So instead of those 4 lines you only need:

Dim appOutLook As Outlook.Application
Dim MailOutLook As Outlook.MailItem
Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)

The last point is that you are creating your email within a loop, so presumably you intend the code to create more than one email. I have a similar procedure in one of my databases. But in order to make it work for more than one email, you need to set your application and mailitem to Nothing, then set them again at the beginning of the next loop.

So just before the Loop you need to do this:

Set appOutLook = Nothing
Set MailOutLook = Nothing

Loop