I have a macro that generates an outlook email based on information in an Excel spreadsheet. There are several string variables that are put in the body of the email depending on specific conditions. One thing that I haven't been able to do has been to transfer a table of information into the body of the email while retaining the order of the table.
I wanted to load a print screen of the table but stack overflow will only allow me to load once I have 10 reputation. The table, however, is just a 6X8 (table on excel arranged the way I want it to be displayed with Range "U2:AB7"). I wanted to find a way to add the info info in the table, as a table, in to the mailbody variable.
The code I have been Using is as follows:
Sub email()
Dim name1 As String
Dim name2 As String
Dim name3 As String
Dim name4 As String
Dim name5 As String
Dim desc1 As String
Dim desc2 As String
Dim desc3 As String
Dim desc4 As String
Dim desc5 As String
Dim years As String
Dim nomes As Integer
Dim mailsubject As String
Dim mailbody As String
name1 = Range("nome1").Value
name2 = Range("nome2").Value
name3 = Range("nome3").Value
name4 = Range("nome4").Value
name5 = Range("nome5").Value
years = Range("Q28").Value
nomes = Range("Q30").Value
desc1 = Range("B3").Value
desc2 = Range("B11").Value
desc3 = Range("B18").Value
desc4 = Range("B26").Value
desc5 = Range("B33").Value
Dim OutlookApp As Outlook.Application
Dim MItem As Outlook.mailitem
Set OutlookApp = New Outlook.Application
If nomes = 3 Then
mailsubject = "PRODUCT IDEA: " & years + "YR PHOENIX AUTOCALL ON " + name1 + " " + name2 + " " + name3
mailbody = "Bom Dia," & vbNewLine & vbNewLine & "Seguem os detalhes das companhias subjacentes da nota acima." & vbNewLine & vbNewLine _
& name1 & ": " & desc1 & vbNewLine & vbNewLine & name2 & ": " & desc2 & vbNewLine & vbNewLine & name3 & ": " & desc3
ElseIf nomes = 5 Then
mailsubject = "PRODUCT IDEA: " & years + "YR PHOENIX AUTOCALL ON " + name1 + " " + name2 + " " + name3 + " " + name4 + " " + name5
mailbody = "Bom Dia," & vbNewLine & vbNewLine & "Seguem os detalhes das companhias subjacentes da nota acima." & vbNewLine & vbNewLine _
& name1 & ": " & desc1 & vbNewLine & vbNewLine & name2 & ": " & desc2 & vbNewLine & vbNewLine & name3 & ": " & desc3 & vbNewLine & vbNewLine _
& name4 & ": " & desc4 & vbNewLine & vbNewLine & name5 & ": " & desc5
End If
Set MItem = OutlookApp.CreateItem(olmailitem)
With MItem
.Subject = mailsubject
.To = "[email protected]"
.body = mailbody
.Save
.Display
End With
End Sub
You can do it with HTML:
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments