Excel template to send a standardised mail

[MaxTiger]

Hi all,

I like to experiment in excel to learn new things (as I am still relatively unexperienced), but sometimes I feel I am trying to reinvent the wheel. This is also the case for what I am trying to accomplish right now.

See, I want to create an excel template with “fill-in-the-blanks” boxes to create a standardized outlook e-mail for purchase requests for me and my colleagues. When the blanks are filled in, the user of the excel (e.g. me) only has to push a “send” button. Then, when the superior that has authorization to actually purchase the indicated products receives the mail, he can easily fulfil the request (easy, because of a standardised form for requests).

The request format should generate a mail looking something like this, with the underlined text as filled-in blanks:

''

Hi Richard,

I have the following purchase request(s) for you, regarding the production of apple pies.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Product ID[/TD]
[TD]Product description[/TD]
[TD]Quantity[/TD]
[TD]Supplier ID[/TD]
[TD]Req. delivery date[/TD]
[/TR]
[TR]
[TD]22023[/TD]
[TD]Butter[/TD]
[TD]2[/TD]
[TD]076[/TD]
[TD]Monday, April 29[/TD]
[/TR]
[TR]
[TD]83391[/TD]
[TD]Pastry[/TD]
[TD]9[/TD]
[TD]339[/TD]
[TD]Friday, April 26[/TD]
[/TR]
[TR]
[TD]4653[/TD]
[TD]Eggs[/TD]
[TD]50[/TD]
[TD]23[/TD]
[TD]Monday, May 6[/TD]
[/TR]
[TR]
[TD]etc.[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


If you have any questions about my order request, please contact me on 079-44251826.

Thank you in advance,

Greetings Max

''

I think you get the idea. It seems very likely to me that someone, somewhere already ‘developed’ something very similar to this. I hope that that someone can share this with me, so I can tweak it to my intended use. Thank you in advance!

Max

 

[Logit]

.
Paste into a Regular Module :

Option Explicit


Sub send_email_via_outlook()


' Tools - Refrence - Microsoft Outlook
Dim olApp As New Outlook.Application
Dim olMail As MailItem


Set olMail = olApp.CreateItem(olMailItem)
    
    With olMail
        .To = "myemail@yahoo.com"
        .CC = ""
        .Subject = "Send Range as table in outlook"  '<br> used to insert a line ( press enter)
        .HTMLBody = "Hi Richard, <br> <br>" & "I have the following purchase request(s) for you, regarding the production of " & Sheet1.Range("G2").Value & ".<br><br> " & _
                    create_table(Sheets("Sheet1").Range("A1").CurrentRegion) & _
                    "</Table><br> <br>If you have any questions about my order request, please contact me on 079-44251826. <br> <br>" & _
                    "Thank you in advance,<br> <br>" & _
                    "Greetings Max<br><br>"
        .Display
        '.Send
    End With




End Sub


Function create_table(rng As Range) As String


Dim mbody As String
Dim mbody1  As String
Dim i As Long
Dim j As Long


' for html color codes list visit http://www.w3schools.com/html/html_colornames.asp


mbody = "<TABLE width=""65%"" Border=""1"", Cellspacing=""0""><TR>" ' configure the table


'create Header row
For i = 1 To rng.Columns.Count
    mbody = mbody & "<TD width=""100"", Bgcolor=""#A52A2A"", Align=""Center""><Font Color=#FFFFFF><b><p style=""font-size:18px"">" & rng.Cells(1, i).Value & " </p></Font></TD>"
Next


' add data to the table
For i = 1 To rng.Rows.Count
    mbody = mbody & "<TR>"
    mbody1 = ""
    For j = 1 To rng.Columns.Count
    mbody1 = mbody1 & "<TD><center>" & rng.Cells(i, j).Value & "</TD>"
    Next
    mbody = mbody & mbody1 & "</TR>"
Next


create_table = mbody
End Function

Download workbook : https://www.amazon.com/clouddrive/share/slBxxdMjF9OrvIPeenoRgZJ7rzVI6qCHuPrJ9aYqyea

 

[MaxTiger]

Thank you Logit,

This is great. I will check if I can tweak this to my preferences!

Max

 

[Logit]

.
You are welcome. Glad to help.