Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Editing Outlook Macro Body

0

Hi,

So I am new to VBA and want to edit the macro provided on this site that sends emails. 

Is it possible to have a .body = "here is a list" and also include a data range from excel? such as Range("A1:D4") 

Ultimately I want the format to look kinda like the below:

'Here is a list:

"Excel input" '

Here is what I have so far:

Sub Email_From_Excel_More_OptionsA3()


Dim emailApplication As Object
Dim emailItem As Object

Set emailApplication = CreateObject("Outlook.Application")
Set emailItem = emailApplication.CreateItem(0)


emailItem.To = Range("B3").Value

'emailItem.CC = "email@test.com"

'emailItem.BCC = "email3@test.com"

emailItem.Subject = Range("C3").Value

emailItem.Body = "Number of your direct reports with late training: " & vbNewLine & "Number of total courses overdue:" & vbNewLine & "List of direct reports with overdue courses:"
'Range("D3:D4").Value

emailItem.Attachments.Add ActiveWorkbook.FullName



'emailItem.Send
emailItem.Display

Set emailItem = Nothing
Set emailApplication = Nothing

End Sub

Please let me know if you guys can offer any advice for .body/email body inputs. 

Thank you!

Answer
Discuss

Answers

0

Using Range("D3:D4").Value won't work in your trial macro but in the attached file, I've added a new variable as follows:

Dim BodyText As String

Then, before populating the email, I've added this simple code to take data from that D3:D4 range:

Range("$d$3:$D$3").Select
For n = 0 To 1
    BodyText = BodyText & vbTab & Selection.Offset(n, 0).Value & vbNewLine
Next n

Here we select the first cell of the range, then build up BodyText as the list of values from the range using a For/Next loop, with tabs added (so it appears indented in the email body). The counter starts from 0 since I've used the Offset function to move between cells (it starts at 0 so looks at the cell +n (=0) rows and + 0 columns from D3, then next time around the loop it looks at the cell +1 rows and + 0 columns from D3 i.e. E3. (You'd need to incease the upper limit of the loop for more vertical cells or detect how many there are).

BodyText can then be added to your existing line, using the bit in bold below:

emailItem.Body = "Number ....  & "List of direct reports with overdue courses:" & vbNewLine & BodyText

As a (perhaps unnecessary) nicety I've put imaginary entries in the cells you mention and also modified your email subject line to add today's date (to give the email context).

Also I notice that you're attaching the Excel file in the email (so please be sure to save it before running the macro or do that within the code). 

If you want to collect information from a range which is more than one column (e.g. A1:D4 as you say in the question), that can be done too but I suggest you need to think how it will appear in your email before you try that. A "For Each.. Next" loop would work okay there.

Hope this does the trick for you.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login