VBA Transfering Values from an Excel table to a String

Bruno Simon Costa

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

Excel, transfering data from one cell to another

From Dev

Getting string from another class and transfering it to url

From Dev

Importing values from excel into VBA

From Dev

Getting garbage values when transfering file from server to client in c?

From Dev

Transfering values from an object in a ArrayList to an array sudoku board

From Dev

Write a delimited string to worksheet table Excel VBA

From Dev

Populate a one-dimensional array with values from an Excel Table column using VBA

From Dev

How can I access selected report filter values in Excel Pivot table from VBA code or Formulas?

From Dev

Displaying some values from an array VBA excel

From Dev

Excel-VBA: Combine String-Number from Multi Column Table

From Dev

VBA Excel Copy Content From Word Table

From Dev

If table not in Access database from Excel VBA

From Dev

VBA Excel Copy Content From Word Table

From Dev

Read Table from HTML in Excel with vba

From Dev

Excel VBA Remove Filter From Table

From Dev

Quotations in Access String from Excel VBA

From Dev

Excel VBA: Formula Not Entering Correctly From String

From Dev

Format String from TextBox to TimeValue in Excel VBA

From Dev

Excel VBA extract words from string

From Dev

Excel VBA - Insert XML From String

From Dev

How to calculate values dynamically from Excel table

From Dev

Getting Summation from a Table, with matching values from another Table in Excel

From Dev

Excel 2013 VBA Pivot Table Filters will change but values wont show

From Dev

Update access table with values from another table using VBA

From Dev

AnyLogic not properly reading from Excel String Values

From Dev

How to add multiple string values to a cell in a excel sheet using vba

From Dev

How to retrieve values from junction table as a String?

From Dev

Concatenate values from column in related table into a string

From Dev

Excel VBA - Paste as values

Related Related

  1. 1

    Excel, transfering data from one cell to another

  2. 2

    Getting string from another class and transfering it to url

  3. 3

    Importing values from excel into VBA

  4. 4

    Getting garbage values when transfering file from server to client in c?

  5. 5

    Transfering values from an object in a ArrayList to an array sudoku board

  6. 6

    Write a delimited string to worksheet table Excel VBA

  7. 7

    Populate a one-dimensional array with values from an Excel Table column using VBA

  8. 8

    How can I access selected report filter values in Excel Pivot table from VBA code or Formulas?

  9. 9

    Displaying some values from an array VBA excel

  10. 10

    Excel-VBA: Combine String-Number from Multi Column Table

  11. 11

    VBA Excel Copy Content From Word Table

  12. 12

    If table not in Access database from Excel VBA

  13. 13

    VBA Excel Copy Content From Word Table

  14. 14

    Read Table from HTML in Excel with vba

  15. 15

    Excel VBA Remove Filter From Table

  16. 16

    Quotations in Access String from Excel VBA

  17. 17

    Excel VBA: Formula Not Entering Correctly From String

  18. 18

    Format String from TextBox to TimeValue in Excel VBA

  19. 19

    Excel VBA extract words from string

  20. 20

    Excel VBA - Insert XML From String

  21. 21

    How to calculate values dynamically from Excel table

  22. 22

    Getting Summation from a Table, with matching values from another Table in Excel

  23. 23

    Excel 2013 VBA Pivot Table Filters will change but values wont show

  24. 24

    Update access table with values from another table using VBA

  25. 25

    AnyLogic not properly reading from Excel String Values

  26. 26

    How to add multiple string values to a cell in a excel sheet using vba

  27. 27

    How to retrieve values from junction table as a String?

  28. 28

    Concatenate values from column in related table into a string

  29. 29

    Excel VBA - Paste as values

HotTag

Archive