Access 2010 - Email VBA

[Jonny45wakey]

Hi all

This may have been requested previously but i couldn't find anything which answers my search for help!

I have developed an Access Database in Access 2010 and have a query called "qryGate1Email" which contains the following fields:-
[UserName]
[EmailAddress]

I would like to use a Run Code macro to initiate a single email to each user within the "qryGate1Email" which has an email address in the "EmailAddress" field.

The email should send through Outlook 2010 and should contain:-

1. Specific email account from which to send email eg: "user1@catnet.com" as i have 2 in my outlook account

2. Email Subject "text"
3. Email Body "text"

Any help much appreciated

Thanks

Jonny

 

[theDBguy]

Hi Jonny. Since you want to use a specific account, you will have to use Outlook Automation. Try looking up the SendUsingAccount method.

 

[Gasman]

Plus have a peek at some of these.

Search results for query: send email

 

[Jonny45wakey]

Hi All

Thanks for your help, can anyone tell me why i am getting this message (see attached) when i run the function sendemailGate1 below?

Public Function SendEMailGate1()
Dim oLook As Object
Dim oMail As Object
Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim strMsg As String

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset("qryGate1EMail", dbOpenForwardOnly)

DoCmd.Hourglass True

Set oLook = CreateObject("Outlook.Application")

Do While Not rst.EOF
Set oMail = oLook.CreateItem(0)
With oMail
strMsg = "Hello " & rst![UserName] & ":" & vbCrLf & vbCrLf & _
"Please log in to BMS and review all GATE 1 Projects where applicable."
.To = rst![EmailAddress]
.Body = strMsg
.Subject = "New GATE 1 Project added to BMS"
.Send 'To SEND and NOT Display
End With
rst.MoveNext
Loop

Set oMail = Nothing
Set oLook = Nothing

rst.Close
Set rst = Nothing
End Function





"qryGate1Email" contains fields USERNAME & EMAILADDRESS which this function looks for and should send an email to the distribution list within the query and email the details contained in the script above.

Any help appreciated

Thanks

Jonny

 

[Gasman]

Do you even have an Outlook object created.?
Have you inspected objects in the debugger?

I looked at my code, but I use the Outlook bound object, however I still add recipients as this link does.?

Automate Outlook using vba: Sending Email from Excel using Outlook

.

 

[Isaac]

Please use code tags when posting. Please indent your code properly to make others able to read it.

Just for testing, can you change this particular block of code to this and let me know what happens?

Do While Not rst.EOF
    Set oMail = oLook.CreateItem(0)
    With oMail
        strMsg = "Hello " & rst![UserName] & ":" & vbCrLf & vbCrLf & _
        "Please log in to BMS and review all GATE 1 Projects where applicable."
        '.To = rst![EmailAddress]
        .Body = strMsg
        .Subject = "New GATE 1 Project added to BMS"
        '.Send 'To SEND and NOT Display
        .Save
        .Display
    End With
    rst.MoveNext
Loop

 

[Isaac]

Another Thing To Try

rst.Fields("EmailAddress").Value

instead of

rst![emailAddress]

 

[Jonny45wakey]

Thanks Isaac, i tried the code but it still returns the same error as previously identified for the .To command.

Regards

Jonny

 

[Deleted member 147267]

Try it with a hard coded and valid email address using the syntax in post 7 to see if the address reference is the issue, or if for some reason .To is not a method of your declared object.
EDIT - because I would have thought that as Object would be too generic and you should use as Outlook.Application, Outlook.MailItem, and perhaps Outlook.Recipient - unless you must use late binding for some reason.

 

[Isaac]

The binding, declarations and Set statements look OK to me, I'm leaning mostly towards either the way you reference the recordset field value or that there simply isn't a valid email address in that record. I would set a breakpoint there and simply view the immediate window, type
?rst.fields("EmailAddress").value (then press Enter)

and see what happens.

I use roughly the same sequence - app as Outlook.Application, mi as app.createitem(0), and then mi.To

@Jonny45wakey I don't think you tried the code posted in #6, because if so, you couldn't get the error on .to, because I have that commented out. I did that so we can see if everything else you try to do with the mailitem failed, too, or if it was just the .to.

 

[Gasman]

I would have thought that To was a property not a method.?

 

[Isaac]

I would have thought that To was a property not a method.?

You're right, although I'm not sure that has any bearing on his problem?

 

[Gasman]

Well the error message stated 'method'. Thought that might be a clue?

 

[Isaac]

Well the error message stated 'method'. Thought that might be a clue?

I see what you mean, but I think the explanation there is just a poorly worded error message from MS, since that seems to be the commonly worded error when .To of a Mailitem fails for people in general.

I would either try the recordset's field .Value to get an explicit string or else set it to a string variable.

 

[Gasman]

I see what you mean, but I think the explanation there is just a poorly worded error message from MS, since that seems to be the commonly worded error when .To of a Mailitem fails for people in general.

I would either try the recordset's field .Value to get an explicit string or else set it to a string variable.

Or just use the Add method and move on.
I generally like to get to the bottom of things, but even I will take another method if it is available.?

 

[Deleted member 147267]

or that there simply isn't a valid email address in that record.

Ja, that's why I suggested using a hard coded valid email address. Just checking it in the immediate window will point out if it's not, but not sure if that proves the .To wont work with Object. I guess there is a reference to Outlook as well?

 

[Isaac]

Or just use the Add method and move on.
I generally like to get to the bottom of things, but even I will take another method if it is available.?

Well I don't entirely disagree, but in this case I don't think the OP has tried even the most minimal troubleshooting steps to figure out why the .To isn't working, which could be very instructive, especially since it's not like the method they are using is obscure or unusual...very common.
The Add might not be any better if OP doesn't resolve the value of the recordset field.

 

[Isaac]

Ja, that's why I suggested using a hard coded valid email address. Just checking it in the immediate window will point out if it's not, but not sure if that proves the .To wont work with Object. I guess there is a reference to Outlook as well?

True. Basic troubleshooting steps have still not be done I don't think.