Consulting

Results 1 to 2 of 2

Thread: Excel vba script to insert data into Outlook

  1. #1
    VBAX Regular
    Joined
    Nov 2012
    Posts
    57
    Location

    Excel vba script to insert data into Outlook

    hello,

    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:

    [VBA]'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
    ActiveSheet.Copy
    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
    .Display
    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[/VBA]

    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.

    Thanks!

  2. #2
    VBAX Guru Kenneth Hobs's Avatar
    Joined
    Nov 2005
    Location
    Tecumseh, OK
    Posts
    4,956
    Location
    [VBA]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
    [/VBA]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •