Mailing Body + Range as Picture to e-mail in file

[Thomazz]

Hello,


I have been writing a lot of macros already, unfortunately never got proper education for it so in practice I'm probably making things too complex. However, in the end always made it work. In this case unfortunately, I would have no clue on how to start so I'm open for any suggestions. Will try to explain what I'm trying to accomplish first.


There is a file that I have that I receive weekly and where I need to mail the owners whenever an amount is looking suspiciously high. This file is thousands of lines and it takes a full day to copy/paste all of these mails. So figured to automate it.


In the file, every "group" (group identifier is in column D) has its own owner. If the field in column S says "Yes", I would need to send a mail to the group owner (full name in column A, firstname in column B and lastname in column C) that this file (which is always the data in a row) is looking rather high. This can be one line (like for groups 2133, 2135 and 2139), but can also be multiple lines (like group 2143). I only want to send 1 mail per group (not per file).


The e-mail should go out to the owner. E-mail addresses are always easy for us as it's firstname.lastname@outlook.com First name and last name are listed in the file already through simple VLOOKUP. The e-mail should say something like :


Dear firstname,


The below files are looking funny.


Best regards,


Thomazz.


Between the second line and the third line i would like to past the piece of the excel files that has all the data - however not all columns (only columns D till O). It's also important the header (row 1) gets repeated. I also want it to be pasted as a picture, so all the Excel formatting stays in place.


In the example I attached, there is 6 times yes in column S and in total 4 e-mails will need to go out.


I would have no clue on how to best approach it. I was thinking I need to pull apart the data first and create new "tabs" for all groups, so at the very least the picture I want to have appear in each mail already is in a separate tab. Next I could point at that tab as it would contain all data for the e-mail.


Anyone that can help/provide some insight ? I'm not asking to write everything, that would be too much to ask (however, if it turns out to be very easy, I will not stop you ), but at the very least would like to get started.

Any input would be very very welcome ! Apologies for the horrible, horrible lay-out of this post. If somebody can tell me if I can share my original Excel, that would make things far far more easier ...


[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 1946"]
<tbody>[TR]
[TD]Name[/TD]
[TD]FirstName[/TD]
[TD]LastName[/TD]
[TD]Group[/TD]
[TD]Groupname[/TD]
[TD]Mode[/TD]
[TD]Source
Mode[/TD]
[TD]Number[/TD]
[TD]Order[/TD]
[TD] Amount1[/TD]
[TD] Amount2[/TD]
[TD] Amount3[/TD]
[TD] Amount5[/TD]
[TD] PeriodAmount[/TD]
[TD] TotalAmount[/TD]
[TD] Over 5 ?[/TD]
[TD] Known ?[/TD]
[TD] Corrected ?[/TD]
[TD] Mail ?[/TD]
[/TR]
[TR]
[TD]Doe, Jane[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[TD]2133[/TD]
[TD]Optimus Prime[/TD]
[TD]Air[/TD]
[TD]571000[/TD]
[TD="align: right"]244217999[/TD]
[TD="align: right"]198481264[/TD]
[TD] (3,623.55)[/TD]
[TD] (3,623.55)[/TD]
[TD] (10,200.04)[/TD]
[TD] (10,200.04)[/TD]
[TD] 2,931.03[/TD]
[TD][/TD]
[TD] No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Doe, Jane[/TD]
[TD]Jane[/TD]
[TD]Doe[/TD]
[TD]2133[/TD]
[TD]Optimus Prime[/TD]
[TD]Ocean[/TD]
[TD]541000[/TD]
[TD="align: right"]237651123[/TD]
[TD="align: right"]191575668[/TD]
[TD] 13,924.79[/TD]
[TD] 18,426.18[/TD]
[TD] 13,924.79[/TD]
[TD] 18,426.18[/TD]
[TD] 11,833.56[/TD]
[TD] 15,652.58[/TD]
[TD] Yes[/TD]
[TD] No[/TD]
[TD] No[/TD]
[TD] Yes[/TD]
[/TR]
[TR]
[TD]Ickx, John[/TD]
[TD]John[/TD]
[TD]Ickx[/TD]
[TD]2135[/TD]
[TD]Bumblebee[/TD]
[TD]Multiple[/TD]
[TD]Multiple[/TD]
[TD="align: right"]241307370[/TD]
[TD="align: right"]195403839[/TD]
[TD] 68,898.71[/TD]
[TD] 112,701.64[/TD]
[TD] 138,247.01[/TD]
[TD] 225,977.88[/TD]
[TD] 58,364.01[/TD]
[TD] 95,468.95[/TD]
[TD] Yes[/TD]
[TD] No[/TD]
[TD] No[/TD]
[TD] Yes[/TD]
[/TR]
[TR]
[TD]Ickx, John[/TD]
[TD]John[/TD]
[TD]Ickx[/TD]
[TD]2135[/TD]
[TD]Bumblebee[/TD]
[TD]Air[/TD]
[TD]571000[/TD]
[TD="align: right"]244330624[/TD]
[TD="align: right"]198600015[/TD]
[TD] (10,267.80)[/TD]
[TD] (10,267.80)[/TD]
[TD] (19,434.54)[/TD]
[TD] (19,434.54)[/TD]
[TD] 1,293.47[/TD]
[TD] 1,293.47[/TD]
[TD] No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ickx, John[/TD]
[TD]John[/TD]
[TD]Ickx[/TD]
[TD]2135[/TD]
[TD]Bumblebee[/TD]
[TD]Multiple[/TD]
[TD]Multiple[/TD]
[TD="align: right"]242074810[/TD]
[TD="align: right"]196206523[/TD]
[TD] 4,488.60[/TD]
[TD] 6,984.93[/TD]
[TD] 9,113.34[/TD]
[TD] 14,106.01[/TD]
[TD] 3,348.61[/TD]
[TD][/TD]
[TD] No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ickx, John[/TD]
[TD]John[/TD]
[TD]Ickx[/TD]
[TD]2135[/TD]
[TD]Bumblebee[/TD]
[TD]Multiple[/TD]
[TD]Multiple[/TD]
[TD="align: right"]243701128[/TD]
[TD="align: right"]197935690[/TD]
[TD] 5,845.35[/TD]
[TD] 5,845.35[/TD]
[TD] 11,825.70[/TD]
[TD] 11,825.70[/TD]
[TD] 4,893.75[/TD]
[TD] 4,893.75[/TD]
[TD] No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Ickx, Jane[/TD]
[TD]Jane[/TD]
[TD]Ickx[/TD]
[TD]2137[/TD]
[TD]Megatron[/TD]
[TD]Multiple[/TD]
[TD]Multiple[/TD]
[TD="align: right"]240566818[/TD]
[TD="align: right"]194630519[/TD]
[TD] 2,656.24[/TD]
[TD] 4,415.22[/TD]
[TD] 8,324.81[/TD]
[TD] 10,083.79[/TD]
[TD] 2,257.14[/TD]
[TD] 3,750.01[/TD]
[TD] No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Unknown, Eric[/TD]
[TD]Eric[/TD]
[TD]Unknown[/TD]
[TD]2138[/TD]
[TD]Starscream[/TD]
[TD]Multiple[/TD]
[TD]Multiple[/TD]
[TD="align: right"]241084859[/TD]
[TD="align: right"]195173298[/TD]
[TD] 424.40[/TD]
[TD] 15,384.80[/TD]
[TD] (12,199.04)[/TD]
[TD] 2,597.68[/TD]
[TD] (10,483.43)[/TD]
[TD][/TD]
[TD] No[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Unknown, Emma[/TD]
[TD]Emma[/TD]
[TD]Unknown[/TD]
[TD]2139[/TD]
[TD]Soundwave[/TD]
[TD]Air[/TD]
[TD]571000[/TD]
[TD="align: right"]243262051[/TD]
[TD="align: right"]197474186[/TD]
[TD] 8,587.80[/TD]
[TD] 8,587.80[/TD]
[TD] 16,932.06[/TD]
[TD] 16,932.06[/TD]
[TD] 7,232.03[/TD]
[TD] 7,232.03[/TD]
[TD] Yes[/TD]
[TD] No[/TD]
[TD] No[/TD]
[TD] Yes[/TD]
[/TR]
[TR]
[TD]Unknown, Albert[/TD]
[TD]Albert[/TD]
[TD]Unknown[/TD]
[TD]2143[/TD]
[TD]Ironhide[/TD]
[TD]Multiple[/TD]
[TD]Multiple[/TD]
[TD="align: right"]244202471[/TD]
[TD="align: right"]198465011[/TD]
[TD] (8,527.07)[/TD]
[TD] (8,527.07)[/TD]
[TD] (17,006.83)[/TD]
[TD] (17,006.83)[/TD]
[TD] 5,323.46[/TD]
[TD] 5,323.46[/TD]
[TD] Yes[/TD]
[TD] No[/TD]
[TD] No[/TD]
[TD] Yes[/TD]
[/TR]
[TR]
[TD]Unknown, Albert[/TD]
[TD]Albert[/TD]
[TD]Unknown[/TD]
[TD]2143[/TD]
[TD]Ironhide[/TD]
[TD]Multiple[/TD]
[TD]Multiple[/TD]
[TD="align: right"]244202327[/TD]
[TD="align: right"]198464900[/TD]
[TD] (9,065.75)[/TD]
[TD] (9,065.75)[/TD]
[TD] (17,018.20)[/TD]
[TD] (17,018.20)[/TD]
[TD] (7,688.70)[/TD]
[TD] (7,688.70)[/TD]
[TD] Yes[/TD]
[TD] No[/TD]
[TD] No[/TD]
[TD] Yes[/TD]
[/TR]
[TR]
[TD]Unknown, Albert[/TD]
[TD]Albert[/TD]
[TD]Unknown[/TD]
[TD]2143[/TD]
[TD]Ironhide[/TD]
[TD]Ocean[/TD]
[TD]541000[/TD]
[TD="align: right"]242208952[/TD]
[TD="align: right"]196351645[/TD]
[TD] 10,820.43[/TD]
[TD] 10,820.43[/TD]
[TD] 10,820.43[/TD]
[TD] 10,820.43[/TD]
[TD] 9,123.16[/TD]
[TD] 9,123.16[/TD]
[TD] Yes[/TD]
[TD] No[/TD]
[TD] No[/TD]
[TD] Yes[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Thanks for any input !

 

[Norie]

Do you definitely want to post the extract from Excel as an image?

If you do it's going to add extra steps to whatever you are doing, namely saving the extract as an image and attaching it to the email.

There is code out there, for example Mailing Range or Example, that will send a range from Excel as HTML in the body of an email.

It might not preserve the formatting perfectly due to the conversion but it might be more straightforward than using an image.

 

[Thomazz]

Thanks for your reply, Norie.

I have stumbled across that piece of code as well. I think I'm fine if using the HTML as well, as it's still better doing it like that compared to spending hours on it.

My biggest problem however is that even that code is using a lot of functions I have never heard of, so I would need to customize that for my file. Have been experimenting with that, but with no luck ...

 

[Norie]

Oops, sent wrong link this is the right one.

 

[Thomazz]

So, that piece of code is working for me when I manually select a range. A couple of things I can't solve for now :

* I'm not going to manually select ranges for this. Since it's multiple groups that I need to mail, I'm still thinking of pulling apart the source data in per groups. When I have the data in tabs per group, I would probably need to have this macro go over all of these sheets and start mailing (and have the macro identify the first name and last name for the mail-address).
* I would also need to add some text-body in the mail - where would I need to do that ?

Many many questions

 

[Norie]

Do you have code that copies/extracts the data you would want to send in the email?

If you did you could use it with the code that sends a specific range rather than the selected range.

 

[Thomazz]

So an update thanks to Norie.

I wrote a module that pulls apart the data as I want it. I pulled the individual selections apart in separate tabs. Each tab is showing exactly what needs to be mailed. This means at the moment that I have (for my example) 6 tabs. Two tabs that hold my original data (called Overview and Mail), and 4 tabs that have the individual selections for the 4 mails (including header). The name of the tabs is always the group name.

So what I think my macro should do now, is go over these for tabs and call the macro in the link from Norie above. Still two questions :

* How can I go tab-by-tab (so have the macro select the first group name) without it calling for the first two (bulk data tabs) first ?
* Secondly, I'm still struggling with getting some text into that mail as well.

For those interested (and it will show my limited programming skills) this is the code I used to pull the data apart.

Sub Maketabs()
Dim row As Integer
Dim countyes As Integer
Dim i As Integer
Dim j As Integer
Dim ws As Worksheet
Dim branch As String
Cells.Select
ActiveWorkbook.Worksheets("Overview").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Overview").Sort.SortFields.Add Key:=Range("S:S" _
        ), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("Overview").Sort.SortFields.Add Key:=Range("D:D" _
        ), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Overview").Sort
        .SetRange Range("A:S")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
End With
i = 2
            Do While Range("S" & i) = "Yes"
                branch = Range("D" & i)
                j = i
                Do Until Range("D" & j) <> Range("D" & i)
                    j = j + 1
                Loop
               
                
                Range("A" & i, "O" & j - 1).Copy
                Set ws = ThisWorkbook.Sheets.Add(After:= _
                ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
                ws.Name = branch
                Range("A2").Insert Shift:=xlDown
                
                Sheets("Overview").Activate
                Range("A1", "O1").Copy
                Sheets(branch).Select
                Range("A1").Select
                ActiveSheet.Paste
                Sheets("Overview").Activate
                i = j
            Loop

End Sub