Solved: E-mailing from excel

[jackdandcoke]

I've been e-mailing an attachment from excel using the code:


Dim OL As Object
Dim EmailItem As Object
Dim Wb As Workbook

Application.ScreenUpdating = False
Set OL = CreateObject("Outlook.Application")
Set EmailItem = OL.CreateItem(olMailItem)
Set Wb = ActiveWorkbook
Wb.Save
With EmailItem
.Importance = olImportanceHigh
.Subject = "Central Webathon Reporting Oct 3-5"
.Body = "Julia, Attached is the webathon report for the weekend of Oct. 3rd. If there are any issues please contact me at me@mycompany.com or call my cell at xxx-xxx-xxxx"
.To = "whoever@whatever.com"
.CC = "me@mycompany.com"
.Attachments.Add Wb.FullName
.Send
End With

Application.ScreenUpdating = True

Set Wb = Nothing
Set OL = Nothing
Set EmailItem = Nothing

Is there a way using this code to paste three ranges of cells into the .Body instead of just one sentence?

I'd like it to look like:
"Top Performer:"
cells A2:B2

"Hourly Rankings:"
cells A4:B13

"Cumulative Rankings:"
cells A15:B24


My boss pretty much 'Lumberged' me into this....yeah...we're going to need you to work Saturday....:banghead:

[Kenneth Hobs]

.Body = "Julia, Attached is the webathon report for the weekend of Oct. 3rd. If there are any issues please contact me at me@mycompany.com or call my cell at xxx-xxx-xxxx" & vbcrlf & vbcrlf & _
WorkSheets("Sheet1").Range("A1").Value & vbcrlf & _
WorkSheets("Sheet2").Range("A1").Value & vbcrlf & _
WorkSheets("Sheet3").Range("A1").Value

[CreganTur]

Yeah, you can do that. Something like:

Dim topPerformer As String
Dim hourRank As String
Dim cumuRank As String

topPerformer = Sheets("Sheet1").Range("A2:B2").Value
hourRank = Sheets("Sheet1").Range("A4:B13").Value
cumuRank = Sheets("Sheet1").Range("A15:B24").Value

'as part of outlook code- body section
.Body = "Top Performer" & vbCrlf & topPerofmer _
& "Hourly Rankings" & vbCrlf & hourRank _
& "Cumulative Rankings" & vbCrlf & cumuRank

Change ("Sheet1") to be whichever worksheet's name you're working with.

As you can see the above captures your cell range values in variables, which it uses later on in the body section of your email code.

This is untested and without warranty :thumb

[jackdandcoke]

Is there a way I can have this macro execute every hour?

Currently I have it Auto_Run and at the end of the Macro I have it quit the application. Then I have Task Manager open the file every hour.

I would prefer to just leave the application open and every hour it e-mail the report out.

[Bob Phillips]

Look at OnTime in t5he VBA help.

[holshy]

You could use the Application.OnTime Method
Sub RunAndSchedule

'Schedule the Next Run
Application.OnTime EarliestTime:=Now+(1/24), Procedure:="RunAndSchedule"

'Run the Macro
Call SendEmail

End Sub

This sub will run the macro and then set itself up to run again in exactly an hour. If you wanted it to go every hour on the hour then there are some more acrobatics to do.

Also note that this will attempt to run the code each time, whether the last run was successful or not.

[jackdandcoke]

Awesome thank you so much!

[Kenneth Hobs]

Here is an example for each minute. You can see it working this way. I made the Worksheet events public to use the public date.