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

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

<tbody>
</tbody>

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