recipients multiple emails from table (1 Viewer)

Mark_

Longboard on the internet
Local time
Today, 05:29
Joined
Sep 12, 2017
Messages
2,111
The email is intended to show the details of table "tbl_Dispatch" and "tbl_Summary" in the body of the email.

What code is supposed to do this? You do not have code referencing either of these tables. You are filling in a variable with SQL for q_Tab_2222, but you don't do anything with this code.

I'd advise coming up with a naming convention also. The name "q_Tab_2222" is pretty meaningless. Same with "frm_email_5_2" or "frm_entry_3bc".

You also have a "Tbl_Entry_Date" that seems to only hold a single date and an autonumber key. This does seem rather pointless as you would normally store the date in a file rather than a pointer to a date.

You will want to read up on data normalization. Tbl_Dispatch seems to be intended to hold references to 6 locations. This will become very problematic the first time you have a seventh location you will need to deal with. Seems like it should have those location in a child record. Same with "Tbl_test", though I've no idea what it would be used for.
 

lookforsmt

Registered User.
Local time
Today, 16:29
Joined
Dec 26, 2011
Messages
672
Thanks Mark for pointing out the Inconsistency in the db. I am not sure how to answer you as my knowledge in access is very poor. But i will try to explain you hope if that helps you to provide a solution.

You are right, the names of some of the forms are meaningless but when i try to do some changes in the code or form layout, i simply give the name to identify the latest version.

Once i achieve my goal then i apply to the actual project.

From the attached table i just want to give background:

a) i want to achieve is emailing or auto trigger at a fixed time once a day to a set of people whose names are checked in the table "Mail"

b) There would be two summary tables, tbl_Summary and another table similar to tbl_Summary. But i agree definitely not tbl_Dispatch since the location field in near future will only increase and it would be difficult to fit in the screen.

c) The summary tables will be refreshed on daily basis by overwriting when new data is uploaded. I don't intend to save any data in any of the tables.

d) The table tbl_Entry_Date & tbl_Test were part of another example which i have missed to remove from these db.

As i said, i try to learn something in access vba and then apply it in the original db.

Hope the above was clear to you.

In short i only want to achieve is send email with 2 tables in the body of the email and both have fixed fields which will be derived from two separate queries which one of them i have named as q_Tab_2222. The second query would also be similar to this but with slight changes.

For this example if you can advice me the code how to display this on email that would be great.

I am not sure if i have replied to all your concerns. Sorry for the late response by the time i receive your post it was 2am here.
 

Mark_

Longboard on the internet
Local time
Today, 05:29
Joined
Sep 12, 2017
Messages
2,111
I have cut down the app to JUST what is needed to send the Emails. I haven't checked if the Emailing part works yet as I don't have outlook set up on my home computer.

Especially when testing you will want to use meaningful names. Doing so lets you post what you are working on without a lot of confusion from other programmers. It is a really really really really really good habit to get into.

As I am showing with the sample, you won't want to use your temporary tables. The queries themselves work just fine. The sample loops through the queries as I am letting THEM do all the work rather than messing with SQL.

In the future, rather than trying to work out what the SQL should be, get a query that does what you want and use it instead.

I am also using Sub and Function calls to break out what needs to be done into logical pieces. It is far easier to debug a dozen lines of code in a single function than to hunt through hundreds of lines to see where you could have made a mistake.

I would recommend looking at each section, pulling up GOOGLE, and seeing what MSDN says about each part. That can help you identify quickly what else you may want to do or use.
 

Attachments

  • SendEmail.accdb
    528 KB · Views: 135

lookforsmt

Registered User.
Local time
Today, 16:29
Joined
Dec 26, 2011
Messages
672
Thanks Mark, i will go through the db and check through the net for better understanding.

i have referenced the outlook again for the compile error can't find project or library.

Will revert in case of any help required.
I want to thank you for all the support provide and patient to listen to my query.
Thank you very much.
 

lookforsmt

Registered User.
Local time
Today, 16:29
Joined
Dec 26, 2011
Messages
672
Hi! Mark, i am trying to understand the code.

Just wanted to know which part of the code should i change if i want to send the email to outlook .Send or .Display and not display on the form screen itself.
 

lookforsmt

Registered User.
Local time
Today, 16:29
Joined
Dec 26, 2011
Messages
672
HI! all, i have managed to get to send emails to multiple recipients who are ticked marked
 

lookforsmt

Registered User.
Local time
Today, 16:29
Joined
Dec 26, 2011
Messages
672
Below is the code
Code:
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 strGreeting1 As String
    Dim strMsg As String
    Dim sqlString As String
    
    Dim strMsg1 As String
    Dim sqlString1 As String
    Dim asPostTable1 As String
    Dim j As Integer

    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><br>Dispatch Summary<br></i></b>" _

    sqlString = "SELECT * From Q_Dispatch_Summary"
    
    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><b><i>Thanks and Regards</i></b><br>"
'----------------------------------------------------------------------------
    Dim rs1 As ADODB.Recordset
    Set rs1 = New ADODB.Recordset

    sqlString1 = "SELECT * From Q_Returns_Summary"
    
    rs1.Open sqlString1, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
'-----------------------------------------------------------------------------
    strMsg1 = "<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>Source</b></td>" & _
        "<td bgcolor='#7EA7CC'> <b>Deleted</b></td>" & _
        "<td bgcolor='#7EA7CC'> <b>Received</b></td>" & _
        "<td bgcolor='#7EA7CC'> <b>Rejected</b></td>" & _
        "<td bgcolor='#7EA7CC'> <b>Returned</b></td>" '& _
        "</tr>"
    j = 0

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

    strMsg1 = strMsg1 & "<tr>" & _
        rowColor & Nz(rs1.Fields("Entry_Date"), "") & "</td>" & _
        rowColor & Nz(rs1.Fields("VIP_flag"), "") & "</td>" & _
        rowColor & Nz(rs1.Fields("Source"), "") & "</td>" & _
        rowColor & Nz(rs1.Fields("Deleted"), "") & "</td>" & _
        rowColor & Nz(rs1.Fields("Received"), "") & "</td>" & _
        rowColor & Nz(rs1.Fields("Rejected"), "") & "</td>" & _
        rowColor & Nz(rs1.Fields("Returned"), "") & "</td>" & _
        "</tr>"
    rs1.MoveNext
    j = j + 1
    Loop
    strMsg1 = strMsg1 & "</table>"
'---------------------------------------------------------------------------
    asPostTable1 = "<br><b><i>Returns Summary</i></b><br>"
'----------------------------------------------------------------------------

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

    Set rs2 = CurrentDb.OpenRecordset("Select * from Mail where Mail.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").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

        '~~> *.* 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
 

lookforsmt

Registered User.
Local time
Today, 16:29
Joined
Dec 26, 2011
Messages
672
Next i want to know:
a) i want to achieve is emailing or auto trigger at a fixed time once a day to a set of people whose names are checked in the table "Mail"

any suggestions pls
 

Mark_

Longboard on the internet
Local time
Today, 05:29
Joined
Sep 12, 2017
Messages
2,111
I would put a timer on your main form AND have a config record (or table for "When sent").

In the event_Timer, check to see if your time is passed. If it is, check the file to see if you've already sent.

If NOT; Send and update your file with the current date.

If you don't have a way of making sure your program is open all of the time this won't work.
 

lookforsmt

Registered User.
Local time
Today, 16:29
Joined
Dec 26, 2011
Messages
672
Thanks and apology for the late response. but i am not sure how to get to the things that you have mentioned.
 

Mark_

Longboard on the internet
Local time
Today, 05:29
Joined
Sep 12, 2017
Messages
2,111
Thanks and apology for the late response. but i am not sure how to get to the things that you have mentioned.

A search on GOOGLE for access vba timer event should give you a good set of resources for learning how to use timers.

For a configuration record, it is simple a table that normally holds one record only. Alternately you would have a table to track when previous Emails were sent, possibly including the list of recipients.

If you use a single record the table would be the data source for your main form. You would have fields in your table for any variables you would otherwise have as global information. When the form opens ACCESS will load your single record for you. When you send the Email, simply update when you sent it and save the record.

If you are using a table so you can audit when Emails were sent, instead of updating a configuration record you add a record to your Sent Email table.
 

lookforsmt

Registered User.
Local time
Today, 16:29
Joined
Dec 26, 2011
Messages
672
HI!
I have created table with date field and trying to auto email once the time is passed. It triggers email when the time interval is kept 10000 (checking after every 10 sec) but i wanted it to check only after the time is passed.
Below is the code, any suggestions:
Code:
Private Sub Form_Load()

    CurrentDb.Execute "INSERT INTO tbl_EmailSent ( EmailDate, EmailSent ) " & _
        " SELECT Date() AS [EmailDate], 0 AS EmailSent;"

    Me.Requery
    
    If Me.EmailSent >= 1 Then
    
        cmdClose.SetFocus
        Me.cmdEmailSaved_rpt_1.Enabled = False
    End If
    
End Sub

Private Sub Form_Timer()

    Dim RunDateFormat As String
    RunDateFormat = Format(Now(), "HH:MM:SS")

    If TimeValue(Now()) >= ("19:19:00") Then
        ' run your job, then turn off the timer
        Call RunProgramSendEmail
        Me.TimerInterval = 0
    End If

    If Me.EmailSent = 0 Then
        CurrentDb.Execute "UPDATE tbl_EmailSent SET tbl_EmailSent.EmailSent = [EmailSent]+1" & _
            " WHERE (((tbl_EmailSent.EmailDate)=Date()));"
            
        Me.Requery
        
        cmdClose.SetFocus
        Me.cmdEmailSaved_rpt_1.Enabled = False
    End If
'    Call email code
End Sub
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:29
Joined
Aug 30, 2003
Messages
36,118
Personally I wouldn't bother with all the timer event stuff. I'd create a db that sent your emails when it loaded, and then quit. I'd call that from Scheduled Tasks.

That said, your test will fail because time values need # as the delimiter, not quotes.
 

lookforsmt

Registered User.
Local time
Today, 16:29
Joined
Dec 26, 2011
Messages
672
i am not aware of the task scheduler but i think it may be blocked to use in my office.
Can you guide me if to be done with timer
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:29
Joined
Aug 30, 2003
Messages
36,118
Not sure why it would be blocked, as it just automates what you could do manually. It can be done with the timer, it's just more work. It also means there's always code running in the background, and you have to protect against the process running more than once with multiple users. Did you try changing the delimiter?
 

lookforsmt

Registered User.
Local time
Today, 16:29
Joined
Dec 26, 2011
Messages
672
thanks for the guidance, i will check this tomorrow 1st thing in office if i am able to do it through schedule task.

Will check through delimiter now.
 

lookforsmt

Registered User.
Local time
Today, 16:29
Joined
Dec 26, 2011
Messages
672
HI! i have done the below code but it does not recognize the delimiter and triggers email based on the Time Interval which i have set to 300000 (30 secs)

Code:
If TimeValue(Now()) >= #8:20:00 PM# Then
 

Mark_

Longboard on the internet
Local time
Today, 05:29
Joined
Sep 12, 2017
Messages
2,111
TimeValue returns the TIME portion of a Date/Time variable, not the text.

If you were using something like
Code:
IF NOW() => (Date() + TimeValue("#8:00 PM#"))
you would get a better result.

See HERE for more help on the functions.

You need to make sure you are comparing the same types of variables.
 

lookforsmt

Registered User.
Local time
Today, 16:29
Joined
Dec 26, 2011
Messages
672
Thanks and sorry for the late response. i will check and update.
 

Users who are viewing this thread

Top Bottom