email using vba in access looping thru recordset (1 Viewer)

VBANEWBIE

Registered User.
Local time
Today, 05:00
Joined
Oct 17, 2016
Messages
17
I am curenttly using some code I found online and it is working.

I just have two issues that I would like to modify

1) I would like at add a FROM field so that the email comes from that email and not my email

2) I would like to format the "body" of the email in this fashion...

Dear Sir,

thanks for you interest.

Regards,

Me


here is the current code that I am using but just need to tweak..


code is below:


Code:
 Private Sub Send_eMail_McKay_Guar_1_Click()
 Dim MyDb As DAO.Database
Dim rsEmail As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim strEmail As String
Dim strMsg As String
Dim oLook As Object
Dim oMail As Object
 Set MyDb = CurrentDb
Set qdf = MyDb.QueryDefs("qrySHLMG1")
 For Each prm In qdf.Parameters
    prm.Value = Eval(prm.Name)
Next
Set rsEmail = qdf.OpenRecordset()
Set oLook = CreateObject("Outlook.Application")
With rsEmail
        .MoveFirst
        Do Until rsEmail.EOF
        myRecipient = .Fields(0)
            If IsNull(myRecipient) = False Then
                Set oLook = CreateObject("Outlook.Application")
                Set oMail = oLook.createitem(0)
                With oMail
                    .to = "
                    .cc = "
                    .bcc = "
                    .body = "This is ONLY a TEST, See attached TEST"
                    .Importance = 2  'Importance Level  0=Low,1=Normal,2=High
                    .Subject = "THIS IS A Test Email"
                    .Attachments.Add ("C:\Users\jf\Documents\RPTTESTEXPORT\test.pdf")
                    .Send
                End With
            End If
                    .MoveNext
        Loop
End With
Set oMail = Nothing
Set oLook = Nothing
 End Sub
much appreciation, in advance...
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:00
Joined
Sep 21, 2011
Messages
14,037

VBANEWBIE

Registered User.
Local time
Today, 05:00
Joined
Oct 17, 2016
Messages
17
Gasman, thanks for the help

I was able to complete the formatting on the body of the email

however, I was able to get the Outlook Account change to work.

I am running outlook 2016

any further advise you can give?

where exactly do I insert the code that was on that link you provided

thanks
 

VBANEWBIE

Registered User.
Local time
Today, 05:00
Joined
Oct 17, 2016
Messages
17
sorry

should say... I was NOT able to get the Outlook Account change to work
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:00
Joined
Sep 21, 2011
Messages
14,037
You would need to identify which account you need.
This link does it by account number
http://www.rondebruin.nl/win/s1/outlook/account.htm

It is just a property of the mail object, so in your case put inside the With oMail block

I believe that if you know the name of the account from the account list then you can just use that.

.SendUsingAccount = "Gmail Private"

that sort of thing.

As I mentioned, I had to use a template and I am still using the 2003 code.

HTH
 

Users who are viewing this thread

Top Bottom