Email to particular recipients (1 Viewer)

[lookforsmt]

HI! all

This is just an extension of my previous solved question where i wanted to look into table (tbl_emailID) where emails are checked and send emails to all those recipients. This is working fine.

Now i want to change the email recipients only where their locations are matching.

I have added a table, "tbl_DispatchDetails" which consists of location data and it should lookup in table, "tbl_EmailID" for corresponding location and then send email to only those recipients.

Can this be possible, if yes below is the code. Can anyone assist me to which line should i add the new code to this.

Option Compare Database
Option Explicit

Private Sub cmdMail_3bc4_Click()

    Dim mess_body As String, StrFile As String, strPath As String
    Dim appOutLook As Object
    Dim MailOutLook As Object
    Dim rs2 As Recordset
    Dim asEmail As String
    Dim Yes As String
    Dim strGreeting As String
    Dim strMsg As String
    Dim sqlString As String
    
    Dim aBody() As String
    Dim lCnt As Long
    Dim asPostTable As String
    Dim i As Integer
    Dim rowColor As String
    
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset

    strGreeting = "<b><i>Dear All,</i></b><br>" & vbNewLine & vbCrLf & "<br><i>Below is the summary of returns and dispatch status</i><br>" _
    & "<b><i></i></b><br>" _

    sqlString = "SELECT * From qry_EmailReceivedSummary"

    rs.Open sqlString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
'-----------------------------------------------------------------------------
    strMsg = "<table border='1' cellpadding='3' cellspacing='3' style='border-collapse: collapse' bordercolor='#111111' width='800'>" & _
        "<tr>" & _
        "<td bgcolor='#7EA7CC'> <b>Entry_Date</b></td>" & _
        "<td bgcolor='#7EA7CC'> <b>VIP_flag</b></td>" & _
        "<td bgcolor='#7EA7CC'> <b>LocationA</b></td>" & _
        "<td bgcolor='#7EA7CC'> <b>LocationB</b></td>" & _
        "<td bgcolor='#7EA7CC'> <b>LocationC</b></td>" & _
        "<td bgcolor='#7EA7CC'> <b>LocationD</b></td>" & _
        "<td bgcolor='#7EA7CC'> <b>LocationE</b></td>" & _
        "<td bgcolor='#7EA7CC'> <b>LocationF</b></td>" & _
        "<td bgcolor='#7EA7CC'> <b>Total</b></td>" '& _
        "</tr>"
    i = 0

    Do While Not rs.EOF
    If (i Mod 2 = 0) Then
        rowColor = "<td align=center bgcolor='#FFFFFF'> "
    Else
        rowColor = "<td align=center bgcolor='#E1DFDF'> "
    End If

    strMsg = strMsg & "<tr>" & _
        rowColor & Nz(rs.Fields("Entry_Date"), "") & "</td>" & _
        rowColor & Nz(rs.Fields("VIP_flag"), "") & "</td>" & _
        rowColor & Nz(rs.Fields("LocationA"), "") & "</td>" & _
        rowColor & Nz(rs.Fields("LocationB"), "") & "</td>" & _
        rowColor & Nz(rs.Fields("LocationC"), "") & "</td>" & _
        rowColor & Nz(rs.Fields("LocationD"), "") & "</td>" & _
        rowColor & Nz(rs.Fields("LocationE"), "") & "</td>" & _
        rowColor & Nz(rs.Fields("LocationF"), "") & "</td>" & _
        rowColor & Nz(rs.Fields("Total"), "") & "</td>" & _
        "</tr>"
    rs.MoveNext
    i = i + 1
    Loop
    strMsg = strMsg & "</table>"
'---------------------------------------------------------------------------
    asPostTable = "<br><br><b><i>Thanks and Regards</i></b><br>"
'----------------------------------------------------------------------------
    
    Set appOutLook = CreateObject("Outlook.Application")
    Set MailOutLook = appOutLook.CreateItem(0)

    Set rs2 = CurrentDb.OpenRecordset("Select * from tbl_EmailID where tbl_EmailID.Summary_chk=Yes")

    '~~> Change path here
    strPath = "E:\Test Folder1\Reports\"

    With MailOutLook
    asEmail = ""
         Do While Not rs2.EOF
            asEmail = asEmail & rs2.Fields("email_Id_To").Value & "; "
            rs2.MoveNext
        Loop
            .To = asEmail
    If asEmail = "" Then
            MsgBox "NO recipients selected!!!"
        Exit Sub 'Exit the sub routine.
    End If

'            .Subject = "Summary Report for date: " & Format$(Date, "dd-mm-yyyy")
'            .HTMLBody = strGreeting & strMsg & asPostTable1 & strMsg1 & asPostTable
            .HTMLBody = strGreeting & strMsg & asPostTable

        '~~> *.* for all files
        StrFile = Dir(strPath & "*.*")

        Do While Len(StrFile) > 0
            .Attachments.Add strPath & StrFile
        StrFile = Dir
        Loop

            '.DeleteAfterSubmit = True
            .Display
            '.Send
    End With
            MsgBox "Reports have been sent", vbOKOnly
End Sub

Thankyou

 

[pbaldy]

The simplest solution is probably to modify the query to include a join to that table on the location field. That should limit the results to the desired records.

 

[lookforsmt]

Thanks pbaldy, but this would not solve my problem as in table "tbl_dispatchDetails" there is location field which only after matching should email that particular details to that recipient (i.e. other location details should not be emailed except matching location).

i am not sure if can be done through query.

i am attaching my db, i think it has to be through vba code.
if you think it is possible.

thanks

 

[lookforsmt]

I managed to solve the problem.

Just want to add a check once the email is sent to the users in field "EmailSent". in table, "temp_tbl_DispatchDetails"
Any suggestions

I have added my revised version.

Thanks

 

[pbaldy]

What's the purpose of joining the entry date table in the email query? Without it, the recordset should be update-able and you could update it in your recordset.

 

[lookforsmt]

Thanks for the advice, i will remove the entrydate table.

I am facing 2 issues:

1) I am encountered an inconsistent data flowing to outlook. Only the table header flows in outlook. I am not sure why this is happening.

2) I am getting below error:
"a program is trying to access e-mail address information stored in outlook"

i have attached both the snapshot

can you assist pls.
Thanks

 

[pbaldy]

Does your query return a record? I just manipulated a date and got:

Dear All,

Below is the summary of returns and dispatch status
CustomerAC RejectReason DispatchLocation RejectDate
2565971 Payee's name missing Location B 6/11/2018
Thanks and Regards

 

[pbaldy]

See if this helps on the warning:

https://support.microsoft.com/en-us...nd-an-e-mail-message-on-your-behalf-warning-i

 

[lookforsmt]

Thanks, i have checked the link, but unable to follow the instruction due to system limitation on my office laptop. It work fine on my personal laptop.

i recall doing a similiar email but it had difference between that code and this one. In that i was sending email to only the selected recipients and it use to send the entire data, whereas in this i am sending the data based on Dispatch location and sending to corresponding match results in tbl email.
i was able to do this on my office laptop without getting the 2nd error

I hope i am not complicating things here.

 

[pbaldy]

Are you addressing issue 1 from your post 6? If so, make sure your query is returning a record. I made sure it returned one and the email contained data.

 

[lookforsmt]

Thanks pbaldy, firstly sorry for the late response as i was involved in another issue which i posted here and therefore couldnt respond to your last post.
yes i am addressing issue 1 from post 6#

when there is one recipient in multiple rows, it displays the recipient email with the details in the body of the email.

But moment there are more than one recipients it display only the 1st recipients names and DOES NOT display anything in the body of the email. And also it Does not create second email with recipients name.

 

[pbaldy]

In the sample above, how exactly do I recreate the issue?

 

[lookforsmt]

Dear pbaldy, if you can have a look at the Outlook1 image posted in post #6 above. The email gives blank details and only header is displayed if there are multiple locations, but if there is just one location then it shows the location.

I have update revised db with the issue. The email is sent based on location. Currently in the temp table there are LocationA; LocationB; & LocationC.

 

[pbaldy]

In post 7 I posted the results I got in an email, which included a record. This looks like the wrong db.

 

[lookforsmt]

Dear pbaldy, I am extremely sorry about the confusion, i have referred the other post attachment in this one.

i have removed the wrong db and upload the correct one.

 

[lookforsmt]

hi!pbaldy, i have tried to change the code but not able to get this working. will you be able to assit

 

[pbaldy]

I'm not sure how to replicate your situation. In this sample, qryDataToSend does not return any records, so there wouldn't be anything to send.

 

[lookforsmt]

HI! pbaldy,

the attached query has criteria "Date()" on date field and second in the temp_tbl_DispatchDetails i have amended the date as today's date. So now you should get the "qryDataToSend" to give 3 records

RejectDate	DispatchLocation	CustomerAC	RejectReason
20/06/2018	Location B	3086458	Deposit slip not authorized
20/06/2018	Location A	2944264	Payee's name missing
20/06/2018	Location A	2944264	Deposit slip not authorized

The issue here is when there is more than one DispatchLocation, like in the above e.g. Location A & Location B, then the body of the email only the header is displayed(refer to post #6), but when the DispatchLocation is just one location, either Location A or Location B, then i get the details in the body of the email.

I am not sure if it has to due with loop through code?

I have removed the old db and uploaded the updated db in post #13

 

[pbaldy]

I only have a minute right now, but noticed you loop rs1 within a loop of rs1. The inner loop will move it to the end, so the outer loop will not run for any records other than the first. I'll dig deeper after lunch, but that may be your problem.

 

[pbaldy]

Had a minute, that is your problem. Getting rid of the inner loop (just the two lines relating to the loop) and a couple of tweaks provides the attached.