+ Reply to Thread
Results 1 to 5 of 5

Trying to use multiple named ranges in the body of an automatically sent email from Excel

Hybrid View

Jo2710 Trying to use multiple named... 09-17-2014, 10:48 PM
Norie Re: Trying to use multiple... 09-17-2014, 10:54 PM
Jo2710 Re: Trying to use multiple... 09-17-2014, 11:01 PM
Norie Re: Trying to use multiple... 09-17-2014, 11:48 PM
Jo2710 Re: Trying to use multiple... 09-18-2014, 12:01 AM
  1. #1
    Registered User
    Join Date
    06-26-2014
    Location
    Christchurch, New Zealand
    MS-Off Ver
    2010
    Posts
    43

    Talking Trying to use multiple named ranges in the body of an automatically sent email from Excel

    Hi

    I'm trying to send a chunk of information from named ranges in my spreadsheet in the body of my auto email.
    When I step through the code there seem to be a few issues that I can't seem to figure out. The first occurs in the body section 'Invalid use of property' when it gets to Range("JobName"). Another error occurs on the line with "Results: ", error is 'Expected: line number or label statement or end of statement'.
    I've googled my heart out trying to solve this myself but not getting anywhere fast! Any advice would be appreciated.

    Thanks
    Jo


    Dim OutApp As Object
        Dim OutMail As Object
    
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
    
        On Error Resume Next
       
        With OutMail
            .To = "jrussell3@placemakers.co.nz" 'Change to Willie when tested (Despatch)
            .CC = ""
            .BCC = ""
            .Subject = "Delivery Site Specific Hazard Analysis: " & Range("AccountCode") & " " & Range("Customer") & " - " & Range("JobName") & ", " & Range("Address1") & ", " & Range("Address2")
            .Body = "Job:" & vbNewLine_
                    Range ("Customer") & ", " & Range("JobName") & vbNewLine_
                    Range("Address1) & ", " & Range("Address2") & ", " &Range("Address3") & vbNewLine & vbNewLine_
                   "Results:" & vbnewline & _
                   "Overhead Cable, Buildings or Foliage: " & Sheets("Ref").Range("overhead") & vbnewline & _
                   "Narrow Driveway: " & Sheets("Ref").Range("driveway") 
            .Send
        End With
    
        Set OutMail = Nothing
        Set OutApp = Nothing

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Trying to use multiple named ranges in the body of an automatically sent email from Ex

    You are missing spaces and a & after the first new line vbNewLine.

    Actually you are missing that at a few other places and there's a couple of other typos.

    Try this
            .Body = "Job:" & vbNewLine & _
                    Range("Customer") & ", " & Range("JobName") & vbNewLine & _
                    Range("Address1") & ", " & Range("Address2") & ", " & Range("Address3") & vbNewLine & vbNewLine & _
                   "Results:" & vbNewLine & _
                   "Overhead Cable, Buildings or Foliage: " & Sheets("Ref").Range("overhead") & vbNewLine & _
                   "Narrow Driveway: " & Sheets("Ref").Range("driveway")
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    06-26-2014
    Location
    Christchurch, New Zealand
    MS-Off Ver
    2010
    Posts
    43

    Re: Trying to use multiple named ranges in the body of an automatically sent email from Ex

    I must really be having a bad day! That stopped all the errors but when the email arrives the body is actually blank, the info on the subject line appears though. Any ideas?
    Thanks
    Jo

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,644

    Re: Trying to use multiple named ranges in the body of an automatically sent email from Ex

    I can't see why that would happen, unless there was a problem with one of the named ranges.

    What happens if you remove/comment out On Error Resume Next?

  5. #5
    Registered User
    Join Date
    06-26-2014
    Location
    Christchurch, New Zealand
    MS-Off Ver
    2010
    Posts
    43

    Re: Trying to use multiple named ranges in the body of an automatically sent email from Ex

    You were on the money, another typo. It was much easier to fin when I commented out the On Error.
    Thanks so much for your help, I really appreciate it

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Excel can send mail with multiple cells info as email body and subject, befr 3 day of due
    By ExcelUser2707 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-13-2013, 04:29 AM
  2. Replies: 2
    Last Post: 05-09-2013, 09:56 PM
  3. Copy and Paste Union of Ranges/Rows to Outlook Email Body
    By darkhunter in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-10-2012, 01:52 PM
  4. Copying Mulitple Ranges, Pasting to body of Email
    By Master Blaster in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 09-11-2012, 01:57 AM
  5. Replies: 4
    Last Post: 06-11-2012, 06:17 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1