recipients multiple emails from table

[lookforsmt]

ok, i have done some changes and now able to send email, but it will still not show any details on the email body. Any further suggestions

 

[pbaldy]

Unless I'm blind, you don't include strMsg when you set the body.

 

[lookforsmt]

well i am really clueless what the strmsg does as my vba knowledge is almost zero.

You are asking a blind man to cross the busy road and expectation is get to the other side unhurt. I would love to follow your instruction if only i could know atleast 1% of vba.

i try and search the forum or check for samples and if anything matches close to my assignment, then i try to replicate it and seek you guys help.

And i have been really greatful for this.
Hope you can let me know in this case

 

[pbaldy]

It's great to get sample code, but you need to understand it when you incorporate it into your app. You build a table in strMsg, but you don't use it here:

        .HTMLBody = "Thank you for Booking. Please find attached." & vbNewLine & vbNewLine & _
    "Booking Confirmation," & vbNewLine & _
    "Terms and Conditions" & vbNewLine & vbNewLine & _
    "If you have any queries regarding this booking, please get in touch." & vbNewLine & vbNewLine & _
    "Many thanks" & vbNewLine & vbNewLine & _
    "Louise"

so I assume your email body just contains the text above. You need to concatenate strMsg into that text in the desired location. In my experience, when using HTMLBody, you need to use <BR> inside the text instead of vbNewLine outside of it.

 

[lookforsmt]

HI! pbaldy
i have tried to change the code but still not able to get the desired result.

I have managed to get the 1st line and the table along with the attached reports in the email.

i wanted to get the combination of below 2 forms added in frm_entry_3bd
I have attached my db, any help will be appreciated.

frm_email_1a + frm_email_5_1

can you help me to achieve this pls

thanks

 

[Mark_]

IF the "Snapshot" is not generated by the same data you do emailing with, then add it after you test to see if you have Email recipients.

I'd also do the same type of check for the body and attachments as you are doing for the Email address; if there is nothing to send let the user know and exit the sub.

 

[lookforsmt]

Thanks Mark, i have posted the request since i am not aware how to do it, else i wouldn't be here in the first place.

If anyone else out here can help me.

 

[lookforsmt]

HI! all

i am still not able to get this right. There are various examples but i am not able to apply it in my db.

I tried to change the code and getting the below error.

"Run-time error 13" Type Mis-match

Can anyone help me. I will then post the code. Thanks

 

[pbaldy]

Sure, post the code. That error usually occurs when you don't use delimiters appropriate to the data type, so include data types as well.

 

[lookforsmt]

Thanks for the reply
I have table "Mail" which has below fields

- Email_Id is Text field
- Summary_chk is Yes/No field

Error highlights on below line

Set rs = CurrentDb.OpenRecordset("Select * from Mail where Mail.Summary_chk=Yes")

Below is the code

Option Compare Database
Option Explicit

Private Sub cmdMail_9_3_Click()

Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem

Dim appOutLook As Object
Dim MailOutLook As Object

Dim strMsg As String
Dim sqlString As String
Dim sqlString1 As String
Dim StrFile As String
Dim strPath As String
Dim i As Integer
Dim rowColor As String
Dim strGreeting As String
Dim strGreeting1 As String
Dim asEmail As String
Dim Yes As String

Dim rs As ADODB.Recordset
'Set rs = New ADODB.Recordset

Set appOutLook = CreateObject("Outlook.Application")
Set MailOutLook = appOutLook.CreateItem(0)

Set rs = CurrentDb.OpenRecordset("Select * from Mail where Mail.Summary_chk=Yes")

strGreeting = "Dear All, " & vbNewLine & vbCrLf & "Below is the summary of returns and dispatched" & vbNewLine

strPath = "E:\Test Folder1\Reports\"

sqlString = "SELECT * From q_Tab_2222"

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>"

Set olApp = Outlook.Application
Set objMail = olApp.CreateItem(olMailItem)
'---------------------------------------------------
'    With MailOutLook
    With objMail
    asEmail = ""
         Do While Not rs.EOF
            asEmail = asEmail & rs.Fields("email_ID").Value & "; "
            rs.MoveNext
        Loop
            .To = asEmail
    If asEmail = "" Then
            MsgBox "NO recipients selected!!!"
        Exit Sub 'Exit the sub routine.
    End If
'---------------------------------------------------

With objMail
.BodyFormat = olFormatHTML
.HTMLBody = strGreeting & strMsg
'.Body = "This is the body of the message." & vbCrLf & vbCrLf

'.Recipients.Add "user1@email.com;user2@email.com"
.Subject = "Summary Report for date"
'.Send          'if you want to send it directly without displaying on screen
'.Display        'if you want to display before sending
'End With
'        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
'----------------------------------------------------
Set olApp = Nothing
Set objMail = Nothing
End With

End Sub

Thanks

 

[Mark_]

Set rs = CurrentDb.OpenRecordset("Select * from Mail where Mail.Summary_chk=Yes")

should be

Set rs = CurrentDb.OpenRecordset("Select * from Mail where Mail.Summary_chk= 'Yes' ")

If you use

Mail.Summary_chk=Yes

the SQL treats Yes is a number. To make sure it understands that Yes is text, you need to put it in quotes.

Suggestion, create a query that produces the recordset you are looking for. Change to SQL view. Copy the SQL. This way you get to start with a known working string.

 

[pbaldy]

I think the problem is that you've declared an ADO recordset but used DAO syntax.

 

[lookforsmt]

Thanks for the suggestion Mark, but i am still getting below error

i have amended the code as you had provided and now getting the below error

Run-time error '3464'
Date type mismatch in criteria expression

So as advised i have changed to SQL view and now getting below error.

  strSQL = "SELECT Mail " & _
                "From [Mail] " & _
                "WHERE (((Mail.Summary_chk)=Yes)"

Run-time error '91'
Object variable or With block variable not set

 

[lookforsmt]

Thanks pbaldy, but i have no idea about that.

 

[Mark_]

"WHERE (((Mail.Summary_chk)=Yes)"

You are looking at a field that is defined as a Yes/No. It is helpful in these kinds of questions to be clear on what your fields are defined as. I can tell because ACCESS put in the Mail.Summary_chk=Yes.

Please check HERE for more info on ADO OpenRecordSet

as pbaldy posted, I think you are referencing the OpenRecordSet incorrectly.

Prior to this line of code, I would add the following pair of line;

Dim asOpenRecordSet as STRING
asOpenRecordSet = "Select * from Mail where Mail.Summary_chk=Yes"

I would change the line itself to read

Set rs = CurrentDb.OpenRecordset asOpenRecordSet

OpenRecordSet takes multiple optional parameters.

I haven't tried this, but I ran into a similar issue previously that was solved by NOT using parenthesis when passing parameters. I also like using a string to hold all passed values to avoid issues with what is being passed and how its parsed.

 

[lookforsmt]

Thanks Mark, i have tried the suggestions and amended the code but still getting error.
I am adding my db, if you can assist me this Pls.
form name is "frm_entry_3bc to 9_4
Thanks

 

[Mark_]

In your code you have

Set rs = CurrentDb.OpenRecordset asOpenRecordSet

Dim asOpenRecordSet As String
asOpenRecordSet = "Select * from Mail where Mail.Summary_chk=Yes"

This should be

Dim asOpenRecordSet As String
asOpenRecordSet = "Select * from Mail where Mail.Summary_chk=Yes"

Set rs = CurrentDb.OpenRecordset(asOpenRecordSet)

That got passed the first error...

For the next set... what table are you trying to use to build up your body? You are still referencing the Mail table.

 

[lookforsmt]

Thanks Mark for the help.
The mail table is only to send to the recipients where the checkbox is "Yes"

The email is intended to show the details of table "tbl_Dispatch" and "tbl_Summary" in the body of the email.

 

[lookforsmt]

i changed the code and getting the run-time error 3265, "Item not found in this collection"

 

[Mark_]

i changed the code and getting the run-time error 3265, "Item not found in this collection"

Same error I received as your existing code references the MAIL table. Hence why I was asking which table it should reference.