Get Email address from Query table to send emails

atuljadhavnetafim

Active Member
Joined
Apr 7, 2012
Messages
341
Office Version
  1. 365
Platform
  1. Windows
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.


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

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
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'
 
Upvote 0
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.

1660978652354.png
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,224,542
Messages
6,179,421
Members
452,913
Latest member
JWD210

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top