Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 21

Email from Excel Headache

This is a discussion on Email from Excel Headache within the Excel Questions forums, part of the Question Forums category; I'm slowly but surely learning, but VBA is another beast, hehe...

  1. #11
    Board Regular
    Join Date
    Oct 2009
    Posts
    76

    Default Re: Email from Excel Headache

    I'm slowly but surely learning, but VBA is another beast, hehe

  2. #12
    Board Regular
    Join Date
    Apr 2008
    Posts
    528

    Default Re: Email from Excel Headache

    Why would you want to concatenate the recipient list?

    Once your email is created (this line
    Code:
    Set aEmail = CreateItem(olMailItem)
    )

    You can add recipients individually; to use your example of taking address from "Sheet 1" within a range A1:A100. This short code creates a new message and populates the "to list"

    Code:
    Sub CreateMail()
    
    Dim WS As Worksheet, x As Long
    Dim ou As Outlook.Application
    Dim nEm As Outlook.MailItem
    Set ou = New Outlook.Application
    Set nEm = CreateItem(olMailItem)
    Set WS = ThisWorkbook.Sheets("Sheet1")
    
    For x = 1 To 100
        nEm.Recipients.Add (WS.Range("A" & x).Value)
    Next
    
    nEm.Display
    
    End Sub
    You also noted errors (correctly identified by being caused as outlook library access). If you cannot establish a link to that library you can also use "late binding" and that link is no longer required.

    See this VBA references and early binding vs late binding | Excel Matters for more information
    Last edited by ScottR; Dec 28th, 2016 at 08:34 AM.

  3. #13
    Board Regular
    Join Date
    Oct 2009
    Posts
    76

    Default Re: Email from Excel Headache

    Hi ScottR

    I used your code but it did not work

    I have just email addresses from A1 - A100

    Then I added an email icon into the spreadsheet, then assigned your macro to that icon.

    When I click the email icon I get an error in return.

    Am I doing this completely wrong?

    I am not a real newbie, but it seems I am doing something wrong here, yet I have done this before with no problems.

    Could you perhaps dumb it down for me to understand

  4. #14
    Board Regular
    Join Date
    Apr 2008
    Posts
    528

    Default Re: Email from Excel Headache

    Hi

    Did you resolve your library issue? If not then please try this version which is converted to late binding. Other things that can cause failure here is

    1) Any blank cells in A1 - A100
    2) Sheet name is not actually "Sheet 1"

    Could you try and look at those three things and if not resolved post the details of your error message and the failure point?

    Code:
    Sub CreateMail()
    
    Dim WS As Worksheet, x As Long
    Dim ou As object
    Dim nEm As object
    Set ou = CreateObject("Outlook.Application")
    Set nEm = ou.CreateItem(olMailItem)
    Set WS = ThisWorkbook.Sheets("Sheet1")
    
    For x = 1 To 100
        nEm.Recipients.Add (WS.Range("A" & x).Value)
    Next
    
    nEm.Display
    
    End Sub

  5. #15
    Board Regular
    Join Date
    Oct 2009
    Posts
    76

    Default Re: Email from Excel Headache

    Hi ScottR

    I have no idea how to resolve the library issue, if there is one, that is way out of my scope

    However, there are blanks between A1 - A100 cells, and the error coming up from your latest code is "Automation Error" when I click on the email Icon

    I appreciate you guys thinking I know a bit more than I do, but unfortunately the library issue is beyond my comprehension, sorry

    Thanks for the feedback, hopefully we are getting close to resolving this

  6. #16
    Board Regular
    Join Date
    Apr 2008
    Posts
    528

    Default Re: Email from Excel Headache

    Library references are easy. Under VBA its "Tools" then references. Just ensure the correct tick box is ticked; that is not required with late binding though so ignore for now.

    This version ignores blank cells

    Sub CreateMail()

    Dim WS As Worksheet, x As Long
    Dim ou As object
    Dim nEm As object
    Set ou = CreateObject("Outlook.Application")
    Set nEm = ou.CreateItem(olMailItem)
    Set WS = ThisWorkbook.Sheets("Sheet1")

    For x = 1 To 100
    if WS.Range("A" & x).value<>"" then nEm.Recipients.Add (WS.Range("A" & x).Value)
    Next

    nEm.Display

    End Sub

  7. #17
    Board Regular
    Join Date
    Oct 2009
    Posts
    76

    Default Re: Email from Excel Headache

    Hi ScottR

    This work like an absolute Bomb !!!!!

    Thank you so very much, wow, does this save time

    One last question, if I decide to change the cell reference once I finalized this spreadsheet, where would I change the coding

    For instance if I rename "Sheet1" to CRM and change the cell reference to C6 - C100 instead of A1-A100, I changed the below code, but it still pulls through A1-A100

    Sub CreateMail()
    Dim WS As Worksheet, x As Long
    Dim ou As Object
    Dim nEm As Object
    Set ou = CreateObject("Outlook.Application")
    Set nEm = ou.CreateItem(olMailItem)
    Set WS = ThisWorkbook.Sheets("CRM")
    For x = 6 To 100
    If WS.Range("C" & x).Value <> "" Then nEm.Recipients.Add (WS.Range("C" & x).Value)
    Next
    nEm.Display
    End Sub

    Please can you advise where I went wrong here

    Thanks again, this works a treat, but I may just need to tweak it after I am done with this spreadsheet as displayed here
    Last edited by TC-Fireman; Dec 28th, 2016 at 10:07 AM.

  8. #18
    Board Regular
    Join Date
    Oct 2009
    Posts
    76

    Default Re: Email from Excel Headache

    Hi ScottR

    Don't worry, I stupidly got the cell reference wrong, it was H instead of C

    Thank you very much indeed, I really can't thank you enough

    All the best bud

    Regards

    TC

  9. #19
    Board Regular
    Join Date
    Apr 2008
    Posts
    528

    Default Re: Email from Excel Headache

    You're welcome and glad you resolved!

  10. #20
    New Member
    Join Date
    Jul 2016
    Posts
    35

    Default Re: Email from Excel Headache

    Quote Originally Posted by ScottR View Post
    Why would you want to concatenate the recipient list?
    The concatenation is a measure useful if you aren't going to use VBA

Page 2 of 3 FirstFirst 123 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com