Consulting

Results 1 to 3 of 3

Thread: email body to reference a specific cell in sheet

  1. #1
    VBAX Regular
    Joined
    Apr 2021
    Posts
    40
    Location

    email body to reference a specific cell in sheet

    Hi guys,

    I have a code that sends out the excel worksheet via a command button. I want to amend the code so that the text in the body of the email references a specific cell. In other words, I have a job number in cell A9 of my worksheet so I want the '.body' part of the code to reference it, along the lines of 'please find spreadsheet attached for A9'. How would I amend the bold part of the code below?

    Any help appreciated
    
    private sub commandbutton1_click()
        'update 20131209
        
        dim wb1 as workbook, wb2 as workbook
        dim sfilepath as string, sfilename as string
        dim iformat as integer
    
        with application
            .screenupdating = false
            .displayalerts = false
        end with
        
        set wb1 = application.activeworkbook
        activesheet.copy
        set wb2 = application.activeworkbook
        
        sfilepath = environ$("temp")
        sfilename = sfilepath & "\ " & wb1.name
        iformat = wb1.fileformat
        wb2.saveas sfilename, iformat
        wb2.close
        
        with createobject("outlook.application").createitem(0)
            .to = "xxxxx"
            .cc = ""
            .bcc = ""
            .subject = "excel sheet test"
            .body = "Hello" & vbLf & vbLf & "Please find spreadsheet attached for A9." & vbLf & vbLf & "Regards" & vbLf & "Ray"
            .attachments.add sfilename
            .send
        end with
        
        kill sfilename
        
        with application
            .displayalerts = true
            .screenupdating = true
        end with
        
    end sub

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Posts
    7,825
    Location
    Top of my head


       .body = "Hello" & vbLf & vbLf & "Please find spreadsheet attached for " & Range("A9").Value & "." & vbLf & vbLf & "Regards" & vbLf & "Ray"
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Regular
    Joined
    Apr 2021
    Posts
    40
    Location
    Quote Originally Posted by Paul_Hossler View Post
    Top of my head


       .body = "Hello" & vbLf & vbLf & "Please find spreadsheet attached for " & Range("A9").Value & "." & vbLf & vbLf & "Regards" & vbLf & "Ray"
    you sir, are a genius. Thank you!

Posting Permissions

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