+ Reply to Thread
Results 1 to 4 of 4

VBA Macro to run email or task from Excel to Outlook

Hybrid View

slohman VBA Macro to run email or... 07-18-2013, 04:15 AM
Debraj Roy Re: VBA Macro to run email or... 07-18-2013, 04:44 AM
slohman Re: VBA Macro to run email or... 07-18-2013, 04:47 AM
Debraj Roy Re: VBA Macro to run email or... 07-18-2013, 06:58 AM
  1. #1
    Registered User
    Join Date
    03-25-2012
    Location
    Melbourne, Victoria
    MS-Off Ver
    Excel 2007
    Posts
    82

    VBA Macro to run email or task from Excel to Outlook

    Can someone help I'm using the below macro but it will not recognise that I have blank rows.

    I have due dates Column I some cells are blank because they are not due, I have subject in Column A, I have Body of Email in Column B & Column M

    It works fine for the first 10 rows as they have dates but when there is a blank it just doesn't send any further emails. I think I need to change from range to cells but I have tried everything.

    Sub emailTask()
    Dim dateRow As Integer
    With Sheets("Data1")
        dateRow = .Range("I4").End(xlDown).Row
        For i = 4 To dateRow
            If .Range("I" & i).Value < Date And Not .Range("E" & i) = "SENT" Then
                Call emailSue(i)
            End If
            
        Next i
    End With
    End Sub
    
    Sub emailSue(i)
        Dim OutApp  As Object
        Dim OutMail  As Object
        
        On Error GoTo errorKey
        Set OutApp = CreateObject("Outlook.Application")
        Set OutMail = OutApp.CreateItem(0)
        
        On Error Resume Next
        With OutMail
            .Subject = Sheets("Data1").Range("A" & i) & ""
            .Body = Sheets("Data1").Range("B" & i) & " - " & Sheets("Data1").Range("L" & i) & ""
            .Recipients.Add ("sue@safeplay.com.au")
            .Recipients.ResolveAll
            .Send    'direct send, if you want manual send, delete this row
        End With
        Sheets("Data1").Range("E" & i) = "SENT"
        
    ContinueIt:
        Set OutMail = Nothing
        Set OutApp = Nothing
        Exit Sub
    errorKey:
        MsgBox Err.Description
        Resume ContinueIt
    End Sub
    Thanks in advance

    http://www.mrexcel.com/forum/excel-q...ml#post3522523
    http://www.ozgrid.com/forum/showthre...116#post675116

  2. #2
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: VBA Macro to run email or task from Excel to Outlook

    So..

    If .Range("I" & i).Value < Date And Not .Range("E" & i) = "SENT" Then
    You still want to send mail.. if
    I column is Blank..
    Please clarify in which case mail need to send..
    Regards!
    =DEC2HEX(3563)

    If you like someone's answer, click the star to give them a reputation point for that answer...

  3. #3
    Registered User
    Join Date
    03-25-2012
    Location
    Melbourne, Victoria
    MS-Off Ver
    Excel 2007
    Posts
    82

    Re: VBA Macro to run email or task from Excel to Outlook

    Sorry if column reads sent, skip and proceed to next due date

  4. #4
    Forum Expert Debraj Roy's Avatar
    Join Date
    09-27-2012
    Location
    New Delhi,India
    MS-Off Ver
    Excel 2013
    Posts
    1,469

    Re: VBA Macro to run email or task from Excel to Outlook

    Hi Slohman,

    If .Range("I" & i).Value < Date And Not .Range("E" & i) = "SENT" Then
    above code is doing the same..

    If not SENT and Due date is less than TODAY..

    By the way..
    add a
    Option Compare Text
    at the starting of the Module.. so you dont have worry about Sent & SENT

+ 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] Create Outlook task from Excel Macro?
    By Jay Harris in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 08-04-2013, 11:11 PM
  2. Create an Outlook Email and or Task based on a due date.
    By appzattak in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-19-2013, 05:30 PM
  3. Recurring Task / Delayed Email in Outlook 2010
    By sme001 in forum Outlook Formatting & Functions
    Replies: 0
    Last Post: 03-12-2013, 11:41 AM
  4. Excel to Outlook as Delayed Email (was Task) (was Calendar)
    By Rick_Stanich in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 10-30-2008, 11:16 AM

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