View Full Version : Excel vba script to insert data into Outlook

02-28-2013, 08:16 AM

I am not sure where to begin with this one, maybe you guys can help.

Let's say I have two tabs in an excel file. The 2nd tab is full of raw data. The first tab is a summary tab that includes, say, statistics such as mean, median, and mode. I have a script that i can run to generate an email and attach the excel file while populating body and subject. you can see it below:

'begin email creation

'Variable declaration
Dim oApp As Object, _
oMail As Object, _
WB As Workbook, _
FileName As String, MailSub As String, MailTxt As String

'************************************************* ********
'Set email details; Comment out if not required
Const MailTo = ""
Const MailCC = ""
Const MailBCC = ""
MailSub = "Weekly Summary"
MailTxt = "Please see the attached which shows in detail the summary of this weeks statuses. Here are some key stats:"
'************************************************* ********

'Turns off screen updating
Application.ScreenUpdating = False

'Makes a copy of the active sheet and save it to
'a temporary file
Set WB = ActiveWorkbook
FileName = "Code 2 and 3.xls"
On Error Resume Next
Kill "C:\" & FileName
On Error GoTo 0
WB.SaveAs FileName:="C:\Users\jsabo\Desktop\" & FileName

'Creates and shows the outlook mail item
Set oApp = CreateObject("Outlook.Application")
Set oMail = oApp.CreateItem(0)
With oMail
.To = MailTo
.Cc = MailCC
.Bcc = MailBCC
.importance = 2
.FlagDueBy = DateAdd("d", 1, Now)
.Subject = MailSub
.Body = MailTxt
.Attachments.Add WB.FullName
End With

'Deletes the temporary file
WB.ChangeFileAccess Mode:=xlReadOnly
Kill WB.FullName
WB.Close SaveChanges:=False

'Restores screen updating and release Outlook
Application.ScreenUpdating = True
Set oMail = Nothing
Set oApp = Nothing

'end email creation

What i am wondering is, is there a way to pull, say, mean median and mode off of the summary tab and insert it directly into the body of the email automatically? FYI, i would want it to be right where the body says "Here are some key stats:"

For instructions sake, let's say that Mean is Cell A1 of the summary tab, Median is Cell A2 of the summary tab, and mode is Cell A3 of the summary tab.


Kenneth Hobs
02-28-2013, 09:30 AM
Sub ken()
Dim MailTxt As String, s() As String
ReDim s(1 To 4)
s(1) = "Please see the attached which shows in detail the summary of this weeks statuses. Here are some key stats:"
With Worksheets("Summary")
s(2) = "Mean: " & .Range("A1").Value2
s(3) = "Median: " & .Range("A2").Value2
s(4) = "Mode: " & .Range("A3").Value2
End With
MailTxt = Join(s(), vbCrLf)
MsgBox MailTxt
End Sub