Adding CC to my Email

[skilche1]

I am having troubles adding email addresses to my CC box in my email. I am able to get the emails to populate into the "To:" space. Now I know I have the DLookup accessing the same record, but how can I make it so the CC_List puts the emails into the CC of the email?

Thanks

    Dim DailyReport As Integer
    Dim EmailRecipiantsList As String
    Dim RecipiantName As String
    Dim CC_List As String
    Dim sAttachmentName As String
    Dim sCaption As String
    Dim sEmailBody As String
    Dim FileName1 As String
    Dim FilePath1 As String
    Dim strNow1 As String
    strNow1 = cboDate
    strNow1 = Replace(strNow1, "/", "-")
    strNow1 = Replace(strNow1, ":", "_")

    EmailRecipiantsList = DLookup("Notification_email_dept", "qryReportNotification", "ReportNotification_ID")
    CC_List = DLookup("Notification_email_dept", "qryReportNotification", "ReportNotification_ID")

    sCurrentReport = "rptDailyReport"
    sAttachmentName = "Daily Incoming Inspection Report" & " - " & FileName & strNow1
    sCaption = "Daily Incoming Inspection Report" & " - " & FileName & strNow1 & ".pdf"
    sEmailBody = "Hi " & RecipiantName & "," & Chr(13) & Chr(13) & "Please see attached The Daily Inspection Report for" & strNow1


    DoCmd.OpenReport sCurrentReport, acViewPreview, , acHidden
    Reports(DailyReport).Caption = sAttachmentName
    DoCmd.Close acReport, sCurrentReport

    DoCmd.SendObject acSendReport, sCurrentReport, acFormatPDF, EmailRecipiantsList, CC_List, , sCaption, sEmailBody

 

[pbaldy]

Did you mean to look up a different field in that record?

 

[skilche1]

Did you mean to look up a different field in that record?

Yea, in my Query, I have to records. One is EmailNotification, which are contacts with emails that will be placed into the "To" box in the email and the CCNotification are those emails that will be added to the "CC" box in the email.

So I am thinking that I have to pull each record for each type of boxes in the email.

 

[Minty]

Looking at that query you probably have a serious data layout/normalisation problem. You shouldn't need fields called member1, member 2 etc.

Your dlookup criteria (the third element) is not specifying a specific record, just ReportNotification_ID , which won't do anything or will only ever return the first row.
Your second dlookup is identical to the first one, so ditto...

 

[skilche1]

Looking at that query you probably have a serious data layout/normalisation problem. You shouldn't need fields called member1, member 2 etc.

Your dlookup criteria (the third element) is not specifying a specific record, just ReportNotification_ID , which won't do anything or will only ever return the first row.
Your second dlookup is identical to the first one, so ditto...

As to the DLookup issue. I realize that and tried different methods of trying to be specific to indicate the record. I am looking for suggestions.

I have attached my Notification table so you see the layout.

 

[Minty]

I'm afraid your table layout is completely wrong. You should have at least 2 tables - a table for contacts like
tblContacts
MemberID(UniquePK)
NameTxt
EmailAddress
anymore member specific fields.

Then a notifications table
tblNotifications
NotificationID (UniquePK)
NotificationTypeID(FK from a third table)
MemberID (FK from above)

and to break this down correctly a Notification type table
tblNotifyTypes
NotificationID(UniquePK)
NotificationText
NotificationFrequency?
other fields related to the type of notification

You can then have as many or as few notifications per member as required, and can either email them separately or concatenate them based on the notification type to send as either CC or BCC.

 

[skilche1]

Great advice, I'll give that a rip. Thanks. I'll report back.

 

[skilche1]

OK, so I got it to work, but I have another problem I ran into. When I click on the button to email the report, I get the following error message. I can get it to actually open the email with email address as long as I have all 15 people selected to be emailed...

Any ideas?

    Dim DailyReport As Integer
    Dim EmailRecipiantsList As String
    Dim RecipiantName As String
    Dim CC_List As String
    Dim sAttachmentName As String
    Dim sCaption As String
    Dim sEmailBody As String
    Dim FileName1 As String
    Dim FilePath1 As String
    Dim strNow1 As String
    strNow1 = cboDate
    strNow1 = Replace(strNow1, "/", "-")
    strNow1 = Replace(strNow1, ":", "_")

    EmailRecipiantsList = DLookup("Notification_email_dept", "qryEmailTo", "EmailType_ID")
    CC_List = DLookup("Notification_email_dept", "qryEmailCC", "EmailType_ID")


    sCurrentReport = "rptDailyReport"
    sAttachmentName = "Daily Incoming Inspection Report" & " - " & FileName & strNow1
    sCaption = "Daily Incoming Inspection Report" & " - " & FileName & strNow1 & ".pdf"
    sEmailBody = "Hi " & RecipiantName & "," & Chr(13) & Chr(13) & "Please see attached The Daily Inspection Report for" & strNow1


    DoCmd.OpenReport sCurrentReport, acViewPreview, , acHidden
    Reports(DailyReport).Caption = sAttachmentName


    DoCmd.SendObject acSendReport, sCurrentReport, acFormatPDF, EmailRecipiantsList, CC_List, , sCaption, sEmailBody

    
    DoCmd.Close acReport, sCurrentReport

 

[pbaldy]

What do the two email lists contain? Are they valid email strings, valid emails separated by semi-colons?

 

[skilche1]

What do the two email lists contain? Are they valid email strings, valid emails separated by semi-colons?

Yes, they are semi-colons.

 

[pbaldy]

Valid emails addresses, separated by semi-colons? Can you post them here, changing private data?

 

[skilche1]

Valid emails addresses, separated by semi-colons? Can you post them here, changing private data?

Here is where I select the recipients:

TO:

If I select more than 11, it works, but I don't get the last part of the email in the address box...

CC:

 

[pbaldy]

So it works with a few but not with all? I wonder if the string is too long and getting cut off. I'd use this to see what the actual strings being used by the code are. See if the end is chopped off and you're getting a partial address:

http://www.baldyweb.com/ImmediateWindow.htm

 

[skilche1]

That is exactly what is happening, but I don't get the error code. The odd thing is, if I select less than 11, then I get this error code.

 

[skilche1]

OK, so I ran this:

    Dim strSQL As String
    
    strSQL = "SELECT EmailType_ID" & _
 " FROM qryEmailTo" & _
 " WHERE MyField = 0;"

...and got this as a result:

SELECT EmailType_ID FROM qryEmailTo WHERE MyField = 0;

 

[Minty]

Looking at your forms, it looks to me as if you are still storing a numbered list of contacts?

I think until your data is correctly stored it will be difficult to work around this problem.

Can you post up the querys that your look up's are using?

 

[skilche1]

Can you post up the querys that your look up's are using?

OK, not sure how you wanted it Minty, but here they are in .jpg format.

By the way, I changed the Result Type in the Notification_email_dept field (where all email address are combined with semi-colons) from "Text" to "Memo" in my tblReportNotification table to capture unlimited email address. So no more issues with not capturing all cut-off email address at the end.


"To:'

"CC:"

Hope this helps.

Thanks,
Steve

 

[Minty]

Okay, You still have member 1, member2, member 3 in your notification list table. This is not the way to store you email list.

I'll post up a sample layout when I get some spare time, shortly.

 

[skilche1]

Okay, You still have member 1, member2, member 3 in your notification list table. This is not the way to store you email list.

I'll post up a sample layout when I get some spare time, shortly.

Any progress Minty?

 

[Minty]

Not yet, sorry been very busy, I'll have a response for you later tonight.