Get Email address from Query table to send emails

[atuljadhavnetafim]

Hi Expert,

i am using below code to send query (GetData) result to email address mentioned below " .To = "at.jad@pol.com"", it is hard coded now.
but i need to add To and CC field from query (GetData) table result, i have three column for emails which i need to use to send email dynamically.
Email1 = use this id for .To
Email 2 = use this id for .CC
Name = use this for Subject line

i am not expert in VBA but i know this is possible, please suggest the way to send email dynamically since each customer has different email address to be use in To field and CC also different person.

Second requirement is: whatever the result come from query must be attached in Excel/PDF format in mail automatically.

Here GetData is the name of query which running fine as expected.

Sub RTFBodyX()
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Dim fs, f
Dim RTFBody, strTo
Dim MyItem As Outlook.MailItem
Dim MyApp As New Outlook.Application
DoCmd.OutputTo acOutputQuery, "GetData", acFormatHTML, "GetData.htm"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.OpenTextFile("GetData.htm", ForReading)
RTFBody = f.ReadAll
'Debug.Print RTFBody
f.Close
Set MyItem = MyApp.CreateItem(olMailItem)
With MyItem
   .To = "at.jad@pol.com"
   .Subject = "txtSubjectLine"
   .HTMLBody = RTFBody
End With
MyItem.Send
End Sub

 

[welshgasman]

I already showed you one way in your other thread?
How you obtain the data is up to you?, perhaps a DLookUp()?

For the attachment Google 'access vba attach file to email'

 

[atuljadhavnetafim]

Thanks for reply, Yes you reply in another thread but since i m not expert in VBA and then i found above code to send email,
but the problem is the email address mark in To is hard coded so for different customer i need to change email address every time.
since i able to get email address from customer master for particular one customer and that i want to use in To and CC field which must be automatically.

below show Email field, i will add another two column as well in query, just need to take email address from below field and send email and not from hard coded.

 

[welshgasman]

Now crossposted at Email address from query table

 

[atuljadhavnetafim]

Hi Welshgasman,

i know i have posted this in another forum as well, i just need solution to complete my project so i can approach anywhere
i already mentioned that i am not expert in VBA, i did not understand solution provided by you.

 

[welshgasman]

It is as simple as
.To = Email1
.CC = Email2
.Subject = Name 'Name is a reserved word, so I would use something else

So just locate that data.
The other forum suggested a loop for a recordset and I have replied in that forum.
I am not going to reply in both , after this post.