Pulling emails into VB code from table

M

miacino

Registered User.
Local time
Today, 07:54
Joined
Jun 5, 2007
Messages
106
Hi! I have a command button built with code to send an email, as such:
-----------------
Private Sub Command271_Click()

DoCmd.SendObject , "", "", "testemail@here.org;test2@here.org", "", "", "New Hire Offer Made", "Good afternoon," & vbCrLf & vbCrLf & "Attached please find the CV for a potential physician hire. I am sending the offer letter/agreement today, but wanted to get this on your radars:" & vbCrLf & vbCrLf & "Employee: " & [PhysicianHired] & vbCrLf & "Dept: " & [Division] & vbCrLf & "Proposed Hire Date: " & [Actual Start Date] & vbCrLf & "Manager: " & [HiringMgr] & vbCrLf & vbCrLf & "Once I receive the signed agreement, I will let you know." & vbCrLf & "Thanks!" & vbCrLf & "Michele"

End Sub
-----------------------------

What I want to do is to be able to pull all the emails from a table (attached) if field "Communication1" is Yes. (vs. having to type all the emails in the code).

:confused:
Can anyone help me out?

:D Michele
 

Attachments

  • Table_Contacts.JPG
    Table_Contacts.JPG
    47.4 KB · Views: 94
You are amazing!
My only problem, is that in the email body - I want to reference (pull in data) on a couple of fields. It no longer allows that ...
-------------
Private Sub Command312_Click()
Dim strFileName As String
Dim rst As DAO.Recordset
Dim strEMailTo As String

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Set rst = CurrentDb.OpenRecordset("select ContactEmail from Contacts where [1_Comm_OfferLtrSent] = True ")

rst.MoveFirst

Do While Not rst.EOF
strEMailTo = strEMailTo & "; " & rst!ContactEmail

rst.MoveNext

Loop
With MailOutLook
.To = strEMailTo
.Subject = "New Hire Offer Made"
.HTMLBody = "<HTML><BODY><font face=Calibri>Good afternoon,<BR><BR>Attached please find the CV for a potential physician hire. I am sending the offer letter/agreement today, but wanted to get this on your radars:<BR><BR>Employee: [PhysicianHired]<BR><BR>Dept: [Division]<BR><BR>Proposed Hire Date: [Actual Start Date]<BR><BR>Manager: [HiringMgr]<BR><BR>Once I receive the signed agreement, I will let you know.<BR>Thanks!<BR>Michele</b></font></BODY></HTML>"
' .Send
.Display 'Used during testing without sending (Comment out .Send if using this line)
End With

End Sub
------------

This is what I get in the email body: I want [physicianhired] to be the actual physician hired name...

Good afternoon,

Attached please find the CV for a potential physician hire. I am sending the offer letter/agreement today, but wanted to get this on your radars:

Employee: [PhysicianHired]

Dept: [Division]

Proposed Hire Date: [Actual Start Date]

Manager: [HiringMgr]

Once I receive the signed agreement, I will let you know.
Thanks!
Michele
 
You need to build the HTML and concatenate the contents of the fields from the recordset.
Or Replace [PhysicianHired] with the contents of PhysicianHired.

Code: 
.HTMLBody = "<HTML><BODY><font face=Calibri>Good afternoon,<BR><BR>Attached please find the CV for a potential physician hire. I am sending the offer letter/agreement today, but wanted to get this on your radars:"
.HTMLBody = .HTMLBody & "<BR><BR>Employee: " & rst![PhysicianHired]
.HTMLBody = .HTMLBody & "<BR><BR>Dept: " & rst![Division]
.HTMLBody = .HTMLBody & "<BR><BR>Proposed Hire Date: " rst! [Actual Start Date]
.HTMLBody = .HTMLBody & "<BR><BR>Manager: " & rst![HiringMgr]
.HTMLBody = .HTMLBody & "<BR><BR>Once I receive the signed agreement, I will let you know.<BR>Thanks!<BR>Michele</b></font></BODY></HTML>"

I've laid it our like that so you can see how it is constructed.

Or the replace option would be along the lines of

Code: 
.HTMLBody = Replace(.HTMLBody,"[PhysicianHired]",rst![PhysicianHired]

and repeat for each field.?
 
Also you could just use your SendObject code in a loop of the recordset.?
 
This worked wonderfully:

Dim strFileName As String
Dim rst As DAO.Recordset
Dim strEMailTo As String

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(olMailItem)
Set rst = CurrentDb.OpenRecordset("select ContactEmail from Contacts where [3_Comm_FullyExeContractPhys] = True ")

rst.MoveFirst

Do While Not rst.EOF
strEMailTo = strEMailTo & "; " & rst!ContactEmail

rst.MoveNext

Loop
With MailOutLook
.To = strEMailTo
.Subject = "Fully Executed Agreement with CT Children's"
.HTMLBody = "<HTML><BODY><font face=Calibri>"
.HTMLBody = .HTMLBody & "<BR><BR>Dear Dr. " & [Text275] & ":"
.HTMLBody = .HTMLBody & "<BR><BR>I'm attaching a copy of the fully executed and signed agreement for your records.<BR><BR>Please feel free to reach out to me should you have any questions.<BR><BR>Sincerely,<BR>Michele</b></font></BODY></HTML>"
.Display 'Used during testing without sending (Comment out .Send if using this line)
End With

-----
But if I wanted to also pull in an email of the applicant pulling from field [EmployeeEmail] - can't I do this as another .To spot line?

.To = .To & ";" & [EmployeeEmail]

It didn't work.

Or can I and:
.CC = [employeeEmail]

That didn't seem to work either...

Any guidance would be appreciated!
 
Actually the additional .To line did work!

.To = .To & ";" & [EmployeeEmail]

Thank you Gasman! Appreciate your help!
 
That should work, but where is [EmployeeEmail] coming from?
You will notice I used the rst prefix for the body contents? You have not used anything?

Also your email now bears no resemblance to the original post?

If the code is behind a form button I would be using Me.EmployeeEmail
 
For some reason, it would not recognize the rst! prefix.

I kept playing with options - tried the Me! prefix as well. (because the code is on a form behind the button).

Using Access 2016 - so not sure why it was acting this way.
 
The rst prefix is only valid if the field is in the recordset rst ?
For the form I use Me.ControlName
 
You must log in or register to reply here.

Similar threads

A
Sending Email SMTP setting
Replies
7
Views
1,429
ions
I
L
Send looping Email
Replies
3
Views
804
looma123
L
M
VBA output report to PDF and email
Replies
18
Views
1,592
Pat Hartman
P
oxicottin
Solved Out Of Memory error when emailing
Replies
8
Views
463
oxicottin
oxicottin
E
Emmanuel Katto : How to Set Outlook to Open in HTML Format from Access Email
Replies
2
Views
185
Trevor G
Trevor G

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom