Pull email address from table

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

It is me again...

I want to add a function that will email a report, but the TO: and CC: is
stored in a table. Is there a way to access to pull those email address?
 
Sure just either use code to refer to a field on a form or open a recordset
and use code to
assign the TO and CC before sending the email.

Dim strTO as String

strTO = Me.txtTO
or
strTO = RS("ToFieldInDB")


Checkout my email module product if you want to get really advanced or make
your life easier.

Mark
RPT Software
http://www.rptsoftware.com
 
Mark, thanks for your help, but me being anything but a VBA guy, I still can
not get it to work.

Following is my code...

Public Function mcoMRCSummaryEmail()
SendEmail
End Function


Public Sub SendEmail()


Dim strTO As String
Dim strCC As String


strTO = rs("TO:", "qryInvAssessmentMemo")
strCC = rs("CC:", "qryInvAssessmentMemo")


DoCmd.SetWarnings False
DoCmd.SendObject acReport, "rptMRC_Summary",
"SnapshotFormat(*.snp)", "strTO", "strCC", "", "", False, ""


SendEmail_Exit:
Exit Sub

SendEmail_Err:
MsgBox Error$
Resume mcoMRCSummaryEmail_Exit

End Sub

I am getting a "sub or function not defined error".
 
Tim,

Since I'm not sure exactly what you are doing (sending one email, sending
multiple emails etc...).

I would suggest looking at some examples to open recordsets:
See these links for help:
http://www.rptsoftware.com/extras/links/
There are some recordset and sendobject email examples on these websites.

If you want to buy my product to tackle this I can help you out more with
your specifics.

Sorry my code example before was not the full code you would need.

Mark
 
Mark,
I am trying to send one email to multiple folks. The TO and CC names are
stored on a table, which the query qryInvAssessmentMemo runs from to pull
just the names that need to go on the email.

I can't figure out how to pull the recordset from ("TO:",
"qryInvAssessmentMemo") and insert it into the SendObject string.
 
Here's some general code, that's probably close:

Dim db As Database
Dim rs As DAO.Recordset
Dim sql As String
Dim strTO as String
Dim strCC as String

sql = "Select * from qryInvAssessmentMemo"
strTo = ""
strCC = ""

Set db = CurrentDb()
Set rs = db.OpenRecordset(sql, dbOpenDynaset)
If Not (rs.BOF And rs.EOF) Then
rs.MoveFirst
Do While Not rs.EOF
strTo = strTo & rs("FieldTO") & ", "
strCC = strCC & rs("FieldCC") & ", "
rs.MoveNext
Loop
End If
rs.Close

'strip off extr coma at end
strTo = Left(strTo,Len(StrTo) - 2)
strCC = Left(strCC,Len(StrCC) - 2)

'Do email
DoCmd.SendObject acReport, "rptMRC_Summary", "SnapshotFormat(*.snp)",
strTO, strCC, "", "", False, ""


Exit_YourSub:
Set rs = Nothing
Set db = Nothing
Exit Sub

Err_YourSub:
MsgBox Err.Description
Resume Exit_YouSub
 
Mark,
With a small amount of tweaking, I got your code to work!!!

Thanks for all your help!!
 
Glad you got it working! Of course you could of done a customized HTML
email for each customer that looked great and had a tracking of
which customers had good and bad email addresses and timestamp history of
each email that went to each customer with our email module and this code:
Also you could of easily choose to send PDF files insead of snapshot (which
I would recommend).

Result = RPT_CreateSingleFile("rptMRC_Summary",
"C:\Reports\Report1.snp","SNP")

If (Result = "Success") Then
sql = "Select *,""Report1.SNP"" as Filename1 from
qryInvAssessmentMemo"
Call MergeEmail(sql, True, 1, 3, True, "E-Mail Customer Letter")
Else
MsgBox Result, vbOKOnly, "Error Creating Report (to be used as
attachment)"
End If
 
Back
Top