Access 2010 - Email VBA (1 Viewer)

Jonny45wakey

Member
Local time
Today, 01:53
Joined
May 4, 2020
Messages
40
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

I’m here to help
Staff member
Local time
Yesterday, 18:53
Joined
Oct 29, 2018
Messages
21,358
Hi Jonny. Since you want to use a specific account, you will have to use Outlook Automation. Try looking up the SendUsingAccount method.
 

Jonny45wakey

Member
Local time
Today, 01:53
Joined
May 4, 2020
Messages
40
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
 

Attachments

  • email3.jpg
    email3.jpg
    10.9 KB · Views: 175

Isaac

Lifelong Learner
Local time
Yesterday, 18:53
Joined
Mar 14, 2017
Messages
8,738
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?

Code:
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

Lifelong Learner
Local time
Yesterday, 18:53
Joined
Mar 14, 2017
Messages
8,738
Another Thing To Try

rst.Fields("EmailAddress").Value

instead of

rst![emailAddress]
 

Jonny45wakey

Member
Local time
Today, 01:53
Joined
May 4, 2020
Messages
40
Thanks Isaac, i tried the code but it still returns the same error as previously identified for the .To command.

Regards

Jonny
 

Attachments

  • email3.jpg
    email3.jpg
    10.9 KB · Views: 175

Micron

AWF VIP
Local time
Yesterday, 21:53
Joined
Oct 20, 2018
Messages
3,476
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

Lifelong Learner
Local time
Yesterday, 18:53
Joined
Mar 14, 2017
Messages
8,738
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

Enthusiastic Amateur
Local time
Today, 01:53
Joined
Sep 21, 2011
Messages
14,048
I would have thought that To was a property not a method.? :unsure:
 

Gasman

Enthusiastic Amateur
Local time
Today, 01:53
Joined
Sep 21, 2011
Messages
14,048
Well the error message stated 'method'. Thought that might be a clue?
 

Isaac

Lifelong Learner
Local time
Yesterday, 18:53
Joined
Mar 14, 2017
Messages
8,738
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

Enthusiastic Amateur
Local time
Today, 01:53
Joined
Sep 21, 2011
Messages
14,048
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.?
 

Micron

AWF VIP
Local time
Yesterday, 21:53
Joined
Oct 20, 2018
Messages
3,476
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

Lifelong Learner
Local time
Yesterday, 18:53
Joined
Mar 14, 2017
Messages
8,738
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

Lifelong Learner
Local time
Yesterday, 18:53
Joined
Mar 14, 2017
Messages
8,738
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.
 

Users who are viewing this thread

Top Bottom