Results 1 to 4 of 4

Thread: Write excel cell value into new email

Hybrid View

Previous Post Previous Post   Next Post Next Post
  1. #1

    Write excel cell value into new email

    example - I would like the value of cell A1 from an excel worksheet to write into my the body of my email.

    How would I go about this?

    This is the code I have so far. (Found on another thread and slightly modified.)

    Sub SendMailMessage()
        Dim objOLapp As Object, obXLapp As Object
        Dim objMailItem As Object
        Dim strBody As String
        Dim un As String
        Set obXLapp = CreateObject("Excel.Application")
        Set objOLapp = CreateObject("Outlook.Application")
        Set objMailItem = objOLapp.CreateItem(0) '0=mailitem
        un = Environ("USERNAME")
        With obXLapp
            .Visible = True
            .Workbooks.Open ("D:\Documents and Settings\" & un & "\My Documents\MyExcelFile.xls")
        End With
        ' strBody = range("A1").value ??? 
        With objMailItem
            .Body = strBody
        End With
     End Sub
    Last edited by Aussiebear; 02-10-2025 at 02:10 AM.

  2. #2
    VBAX Master Norie's Avatar
    Joined
    Jan 2005
    Location
    Stirling, Scotland
    Posts
    1,831
    Location
    Stephanie

    That's almost right.
    Sub SendMailMessage()
        Dim objOLapp As Object, obXLapp As Object
        Dim obXLWB As Object
        Dim objMailItem As Object
        Dim strBody As String
        Dim un As String
        Set obXLapp = CreateObject("Excel.Application")
        Set objOLapp = CreateObject("Outlook.Application")
        Set objMailItem = objOLapp.CreateItem(0) '0=mailitem
        un = Environ("USERNAME")
        With obXLapp
            .Visible = True
            Set obXLWB = .Workbooks.Open("D:\Documents and Settings\" & un & "\My Documents\MyExcelFile.xls")
        End With
        strBody = obXLWB.ActiveSheet.Range("A1").Value
        With objMailItem
            .Body = strBody
        End With
    End Sub
    Last edited by Aussiebear; 02-10-2025 at 02:12 AM.

  3. #3
    Thanks.

  4. #4
    I have outlook set to attach my signature to all new, fowarded and replied messages. When I create a new email using the above code my signature is not included. How do I include it?

Posting Permissions

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