recipients multiple emails from table (1 Viewer)

lookforsmt

Registered User.
Local time
Tomorrow, 03:18
Joined
Dec 26, 2011
Messages
672
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

Wino Moderator
Staff member
Local time
Today, 16:18
Joined
Aug 30, 2003
Messages
36,118
Unless I'm blind, you don't include strMsg when you set the body.
 

lookforsmt

Registered User.
Local time
Tomorrow, 03:18
Joined
Dec 26, 2011
Messages
672
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

Wino Moderator
Staff member
Local time
Today, 16:18
Joined
Aug 30, 2003
Messages
36,118
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:

Code:
        .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

Registered User.
Local time
Tomorrow, 03:18
Joined
Dec 26, 2011
Messages
672
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
 

Attachments

  • Dispatch1.6b.accdb
    1 MB · Views: 122

Mark_

Longboard on the internet
Local time
Today, 16:18
Joined
Sep 12, 2017
Messages
2,111
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

Registered User.
Local time
Tomorrow, 03:18
Joined
Dec 26, 2011
Messages
672
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

Registered User.
Local time
Tomorrow, 03:18
Joined
Dec 26, 2011
Messages
672
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

Wino Moderator
Staff member
Local time
Today, 16:18
Joined
Aug 30, 2003
Messages
36,118
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

Registered User.
Local time
Tomorrow, 03:18
Joined
Dec 26, 2011
Messages
672
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

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_

Longboard on the internet
Local time
Today, 16:18
Joined
Sep 12, 2017
Messages
2,111
Code:
Set rs = CurrentDb.OpenRecordset("Select * from Mail where Mail.Summary_chk=Yes")
should be
Code:
Set rs = CurrentDb.OpenRecordset("Select * from Mail where Mail.Summary_chk= 'Yes' ")

If you use
Code:
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.
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:18
Joined
Aug 30, 2003
Messages
36,118
I think the problem is that you've declared an ADO recordset but used DAO syntax. ;)
 

lookforsmt

Registered User.
Local time
Tomorrow, 03:18
Joined
Dec 26, 2011
Messages
672
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.

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

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

Mark_

Longboard on the internet
Local time
Today, 16:18
Joined
Sep 12, 2017
Messages
2,111
"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;

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

I would change the line itself to read

Code:
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

Registered User.
Local time
Tomorrow, 03:18
Joined
Dec 26, 2011
Messages
672
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
 

Attachments

  • Test3.accdb
    744 KB · Views: 109

Mark_

Longboard on the internet
Local time
Today, 16:18
Joined
Sep 12, 2017
Messages
2,111
In your code you have

Code:
Set rs = CurrentDb.OpenRecordset asOpenRecordSet

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

This should be

Code:
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

Registered User.
Local time
Tomorrow, 03:18
Joined
Dec 26, 2011
Messages
672
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

Registered User.
Local time
Tomorrow, 03:18
Joined
Dec 26, 2011
Messages
672
i changed the code and getting the run-time error 3265, "Item not found in this collection"
 

Mark_

Longboard on the internet
Local time
Today, 16:18
Joined
Sep 12, 2017
Messages
2,111
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.
 

Users who are viewing this thread

Top Bottom