+ Reply to Thread
Results 1 to 12 of 12

Send email from Excel using Outlook

  1. #1
    Registered User
    Join Date
    06-28-2013
    Location
    London, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    69

    Send email from Excel using Outlook

    Hi all,

    Trying to set up a module that uses the dates from a worksheet and then compiles the data and sends in email form Outlook. I am running into this problem currently:

    "Type Mismatch. Unable to coerce parameter value. Outlook cannot translate your string"

    This error appears at the line .Body = Worksheets("Monitoring Information").Range("A3:A4").Text

    When I change the code to .Value I get the error "Array Lower bound must be zero.

    Any help you can offer with this would be greatly appreciated!



    Sub SendEmail()
    Dim OutApp As Object
    Dim OutMail As Object

    ' If Date - Worksheets("Monitoring Information").Range("A:A") <= Today() + 7 Then (I would like to add this code but I get a compile error that the sub or function is not defined)

    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Set OutMail = OutApp.CreateItem(0)
    With OutMail
    .To = Range("J3").Value
    .Subject = "Upcoming Monitoring Deadlines"
    .Body = Worksheets("Monitoring Information").Range("A3:F20").Value
    '.display
    .Send
    End With

    Set OutMail = Nothing
    Set OutApp = Nothing
    ' End If
    End Sub
    [/Code]

  2. #2
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Send email from Excel using Outlook

    Please Login or Register  to view this content.
    And this:

    Please Login or Register  to view this content.
    Copy into a Module.
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."

  3. #3
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Send email from Excel using Outlook

    Quote Originally Posted by clapforthewolfman View Post
    ' If Date - Worksheets("Monitoring Information").Range("A:A") <= Today() + 7 Then (I would like to add this code but I get a compile error that the sub or function is not defined)
    This is referencing all of column A. Its also missing a property. Which Range (cell) are you trying to compare today's day plus 7 (days?) to?

  4. #4
    Registered User
    Join Date
    06-28-2013
    Location
    London, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Send email from Excel using Outlook

    Thanks for the help Solus! Code works.

    Here is what I am trying to accomplish...

    I have a worksheet which holds information for ongoing projects. One of the fields is a "due date". I would like to have these due dates compare with the system date and when they are within a certain range of Today() this email will send to notify the user that this due date is approaching. The email body would contain all of the cells that meet this condition (say within a week of Today()). This could be accomplished by applying a filter I think.

    Anyway let me know your thoughts. So far this has been very helpful!

    Thanks,

    Wolfman

  5. #5
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Send email from Excel using Outlook

    So if any date in column A is within 7 days then the whole work book gets mailed as a reminder?

  6. #6
    Registered User
    Join Date
    06-28-2013
    Location
    London, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Send email from Excel using Outlook

    Not the entire workbook just the records that have a date within the specified range. This could be accomplished with conditional formatting and filtering I think...

  7. #7
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Send email from Excel using Outlook

    What about dates in the past?

  8. #8
    Registered User
    Join Date
    06-28-2013
    Location
    London, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Send email from Excel using Outlook

    So if a due date is less than Today() date? This is the problem I've been having with my conditional formatting incidentally... I have thought about this and it would be sent maybe in a different email form.

  9. #9
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Send email from Excel using Outlook

    Lets address this one first. I'll assume dates in the past do not make it to email.

  10. #10
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Send email from Excel using Outlook

    Discard the code I previously posted.

    Replace entire module with code below:
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    06-28-2013
    Location
    London, Ontario
    MS-Off Ver
    Excel 2010
    Posts
    69

    Re: Send email from Excel using Outlook

    Can you explain what conditions exist on the filter for column A?

    Please Login or Register  to view this content.

  12. #12
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Send email from Excel using Outlook

    Please Login or Register  to view this content.
    These declare three variables (names I'll use to store values) and their data type Long (Long is any number between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807)

    The code to send the email copies visible cells this code:
    Please Login or Register  to view this content.
    makes sure that the screen updating is turned on so that later when we copy cells they ones mark hidden will update to hidden.

    Please Login or Register  to view this content.
    Sets the variable we declared in the first step to the last occupied row in column A. If there is data in cells in column a clear down to row 9999 then lRow = 9999.

    Please Login or Register  to view this content.
    Starts a loop that steps i (another variable declared in step one) from 4 to whatever lRow is set to (our example was 9999 but it changes based on the number of cells occupied.

    Please Login or Register  to view this content.
    This asks a question if range Ai (remember the value of i varies based on which step of the loop we're in, it has the effect of checking every occupied cell in column) is a date. If this is true then we know it could be one of the cells we want to copy. If it is then we:

    Please Login or Register  to view this content.
    set the last variable we defined to a number which is the difference between todays day date, and the date in the stepped range we're looking at.

    If this comparison is more than 0 (the date is not in the past) and less than 7 (its within our timeframe therefore we want to keep it in the eamil) we can sort them out with
    Please Login or Register  to view this content.
    All the rest of the cells we will make invisible so they are not part of our email. We can accomplish this with:
    Please Login or Register  to view this content.
    The rest of this happens in code that you didn't quote -the call sendemail is another Sub with a function that creates an HTML object consisting of the visible cells in the selected range. Lastly we make all cells visible again so they can be edited if need be.

    I hope this answers your question, if it does please mark that thread as [SOLVED]

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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