Add Data From Excel Sheet to Email HTML Body

**DMT Dave** · 02-18-2025, 08:16 AM

jojowhite, that is very tidy, borders, padding and colours stand out more than my post #29

I am going analyse, particular AppendToBody byRef and try similar in another part of DB

Very tidy

**DMT Dave** · 02-19-2025, 01:48 AM

@jojowhite, I have had a look through your code again, this method is one that i wouldn't have considered at all (going back to using a Private Sub only ever to be called upon)

I have tried another part of the system, just working my way to understand it.

So this isn't a full procedure but i have out put only 1 table required but 15 different Sort Numbers in stock (Sort No Groups Item Type)

These are the parts i have changed in my new event

Code:

Private Sub StockToEmail(ByVal strItemType As String, ByRef strBody As String)
    Dim rs As DAO.Recordset
    Dim intSort As Integer ' Dave Separator for Item Groups 1 to 15
    Dim sql As String
    Dim sFH As String, sFHEnd As String
    Dim strBoxStart As String, strBoxEnd As String
    Dim strFS As String, strFE As String
    Dim sItemType As String
    Dim blankRow As String

sql = "SELECT tblStock.ItemType, tblStock.ItemNo, tblStock.StartQty, tblStock.AllocatedQty, tblStock.NewQty, tblStock.SortNo" _
            & " From tblStock" _
            & " WHERE (((tblStock.SortNo) < 16))" _
            & " ORDER BY tblStock.SortNo;"
        
    With CurrentDb.OpenRecordset(sql, dbOpenSnapshot, dbReadOnly)
        If Not (.BOF And .EOF) Then
            .MoveFirst
            intSort = !SortNo
        End If
        Do While Not .EOF
       
            strBody = strBody & "<tr>" & _
                "<td style='background-color:#F5F5F5;border:1px solid black;font-family:arial;border-collapse:collapse;padding:8px'>" & strFS & !ItemType & strFE & "</td>" & _
                "<td style='background-color:#F8F8FF;border:1px solid black;font-family:arial;border-collapse:collapse;padding:8px'>" & strFS & !ItemNo & strFE & "</td>" & _
                "<td style='background-color:#F5F5F5;border:1px solid black;font-family:arial;border-collapse:collapse;padding:8px'>" & strFS & !StartQty & strFE & "</td>" & _
                "<td style='background-color:#F8F8FF;border:1px solid black;font-family:arial;border-collapse:collapse;padding:8px'>" & strFS & !AllocatedQty & strFE & "</td>" & _
                "<td style='background-color:#F8F8FF;border:1px solid black;font-family:arial;border-collapse:collapse;padding:8px'>" & strFS & !NewQty & strFE & "</td>" & _
                "<td style='background-color:#F5F5F5;border:1px solid black;font-family:arial;border-collapse:collapse;padding:8px'>" & strFS & !SortNo & strFE & "</td></tr>"
       
            .MoveNext
            If Not .EOF Then
                If !SortNo & "" <> intSort Then
                    intSort = !SortNo & ""
                    strBody = strBody & blankRow
                End If
            End If
        Loop
        .Close
    End With
    'strBody = strBody & "</table>" & strBoxEnd & "<br>"
    strBody = strBody & "</table><br>

Then changed the run procedure to (Not added all variables but mainly bits i have changed)

This is only 1 table required with 15 separators of Sort No, works great

In a 1 table instance, do i need (For iSort = 1 to 1 ?) or change to just iSort = 1 ' 1 x HTML table and remove (For statement) ? unless more than 1 table per group then add the For Statement in ?

I am trying to learn this as it is very clean / short coding

Code:

 strBody = "<HTML><Body>"    
    Dim iSort As Integer
    For iSort = 1 To 1 ' Amount Of HTML Tables
        Call StockToEmail(iSort, strBody)' Dave , 1 x table of stock items
    
        'Call AppendToBody(WeekdayName(iDay, False, vbSunday), strBody) 'jojowhite driver weekly emails
    Next

i also need to look up (dbOpenSnapshot, dbReadOnly) in rs, seen it before but not looked up usage and never used, guessing snapshot is take a snip of data and read only is is not editable

**jojowhite** · 02-19-2025, 02:50 AM

if you are making only 1 Html Table with 1 to 15 SortNo on it (each SortNo separated by a blank line),
then you do not need to pass the iSort to it, since StockToEmail sub is not using the first parameter, strItemType
inside it's code, you might just as well take it out from the subs declaration:

Code:

Private Sub StockToEmail(ByRef strBody As String)

now on your Main sub/function there is no need for the For...Next loop.
just call the StockToEmail like this:

Code:

    strBody = "<HTML><Body>"


    ' arnelgp
    ' you can remove the lines that are commented out
    '
    ' there is no need to make a Loop
    'Dim iSort As Integer
    'For iSort = 1 To 1 ' Amount Of HTML Tables
    '    Call StockToEmail(iSort, strBody)' Dave , 1 x table of stock items
    ' 
    '    'Call AppendToBody(WeekdayName(iDay, False, vbSunday), strBody) 'jojowhite driver weekly emails
    'Next
    '
    ' just call it directly
    Call StockToEmail(strBody)

You are right about dbOpenSnapshot and dbReadOnly.
You can google their meanings to better explain it to you.

**DMT Dave** · 02-19-2025, 03:32 AM

got it after change, great thanks you, my next learning point about this is:

the use of ByVal and ByRef

I will google later.....

I can learn a lot from this, may take time but very interesting indeed

**DMT Dave** · 03-02-2025, 12:44 PM

@jojowhite kindly offered a great solution to this thread, i have done a lot with this elsewhere which is great, i have 1 question

I have options on a form (see image), if no options are selected, the image appears to tell a user you have not selected an option

If any options are selected, all works great, if i wanted to select more than 1 option, the email adds 2 body's to the email body by my code below, how do i:

Call 2 Subs and the 2 tables to the email body ?

I have not pasted all the Subs to call due to being a lot (5) for 5 options, all work individually great is another reason not all posted, my stumbling block is trying to add more than 1 option with out a duplicate table onto email ?

All subs Called are:

Private Sub SearchAssign(strBody As String)

Private Sub SearchCollections(strBody As String)

Private Sub SearchStock(strBody As String)

Private Sub SearchEdit(strBody As String)

Have tried renaming the above Subs by adding 1,2,3,4 prefixes on the end of the body and tried calling them, now renamed back to strBody for all Subs

Click image for larger version.

Name: No Selection.JPG
Views: 12
Size: 27.3 KB
ID: 52798

Code:

‘Call Stock And Assign
    If Me.optMailPOD = False Then
        If Me.optMailEdit = False Then
            If Me.optMailAssigned = True Then
                If Me.optMailCollections = False Then
                    If Me.optMailStock = True Then
                    myGreet = TOD
                        If IsNull(Me.txtMFG) Then
                            strSubj = "Complete Item Stock Data"
                            strMSG = "Please Find Complete Item Stock Data"
                        End If
                        If Not IsNull(Me.txtMFG) Then
                            strSubj = "Item Data For " & strCriteria
                            strMSG = "Please find Item Data For " & strCriteria
                        End If
                        Call SearchAssign(strBody) ‘Duplicating body!!!!!!!!!
                        Call SearchStock(strBody) ‘ Not Duplicating
                        strMailBody = strBody
       
                    End If
                End If
            End If
        End If
    End If

If i need to add all Called Sub's i can just take a while to remove sensitive data, as mentioned each options individual is giving the result, once i know the method of the duplicate body, i can then adapt

**madpiet** · 03-02-2025, 01:07 PM

ByVal passes a COPY of the value. ByRef passes a memory address. So if you change something in ByVal, it's on a separate copy of the original, so no changes will be reflected in the original. On the other hand, if you change a variable passed by reference, it will be permanent (changes the value in that memory location reference).

**Welshgasman** · 03-02-2025, 01:50 PM

Dave,
All your logic depends on the other options?
If you can select more than one option, then you need to test each separately?
The fact that one is not selected has no bearing on the others.

**DMT Dave** · 03-02-2025, 02:05 PM

Thanks guy's

@madpiet yes i think the ByVal and ByRef is the key based on my understanding of your suggestion

@WGM

Yeh i have walked thorugh individual options when creating, all results are good, i just know the method i was trying to call x 2 Subs wasn't correct, my initial thought was that they were both subs called upon are Called strBody assigned to local String strMailBody

Add Data From Excel Sheet to Email HTML Body

Thread Tools

Similar Threads

Adding Excel Cells To Email html body

Sending email with Access through Outlook using HTML body

email body using html format

Sending Reports with email body having HTML

Export html to body of email issue

Posting Permissions