Please help me get time back with my kids.

[Vampyredh]

Hello all,

I am new to the wonderful world of visual basic, and I have been having some issues trying to get something to work for me and was hoping on of you may be able to assist me. I got into Visual Basic because I do a lot of emailing based on an excel report I get daily from my company. The report contains many rows of assignments that are due.

I have to read this report check dates in column E against today's date - 5 days, so if the date in column E is less than 5 days from the current date, I have check column F to see if there is an #N/A, if there is I am to send the Person involved in an email. There name can be found in column G. The email is to contain the id number and company name in the subject line which the id can be found in column A and the company in column Z. I have tried numerous forums and threads over the last week looking for help. I am generating 30 to 40 emails per day based on this excel spread sheet. I know that automation is the answer to saving me many hours (I am paid a pittance for pay working 60 plus hours a week 20 of it is spent sending this email) I have included a base example of the workbook no formatting mostly for a visual reference. If any of you could help me out with a visual basic macro to do this for me I would be very humbled as my kids would get to see me a little more. I would even be fine if it created a new word doc that would have this info that I need if an email is not possible.

 

[Eric W]

If that's the case, then the If statement must not be detecting any matches. If you can attach a screen shot, it might help analyze the input data. If you click on the HTML Maker link in my signature, it will show you how to create a screen shot.

Alternatively, you can try a little debugging. Instead of starting the macro with F5, use F8. This will execute it 1 line at a time. Keep pressing F8 to continue. When you get to the If statement, hover the mouse over the Date and MyDate values, and you should see what Excel thinks they are. You might see something useful. Press F5 to just let it run to the end.

Let me know what you find.

 

[Vampyredh]

I will try the debugging option and let you know if I see anything

 

[Vampyredh]

Yeah when it hits Set oApp = CreateObject("Outlook.Application")

Nothing happens. Does it matter that I am using office 2013 pro?

 

[MARK858]

@Vampiredh, do you have a reference to Microsoft Outlook xx.x Object Library (where xx.x us your version number)?

You check under Tools - References and check the checkbox if not already checked.

BTW

Range("G" & Rows.Count).End(xlUp).Row

is more reliable than End(xlDown) as it allows for blank cells in the middle of your data when finding the last row.

 

[Vampyredh]

Microsoft Outlook 15.0 Object Library

 

[MARK858]

Is the box checked?

 

[Vampyredh]

Yes

 

[MARK858]

I'll have another look when I get in (about 3/4 hour)

 

[Vampyredh]

Thanks everyone for your help
I would be lost with out you.

 

[MARK858]

I have tested the code below (and all I changed was the way of deciding the last row) and it creates the email fine for me as long as there is a real date.

Sub MailLoop()
Dim r As Long, oMail As Object, oApp As Object

    'Create and show the outlook mail item
    Set oApp = CreateObject("Outlook.Application")
    Set oMail = oApp.CreateItem(0)
    
    For r = 1 To Range("G" & Rows.Count).End(xlUp).Row
        If Abs(Date - Cells(r, "E")) < 5 And IsError(Cells(r, "F")) Then
            With oMail
                .To = Cells(r, "G")
                .Subject = Cells(r, "A") & " / " & Cells(r, "Z")
                '.cc = ""
                '.bcc = ""
                .Body = "Action required"
                '.Attachments.Add WB.FullName
                .Display
                'Stop
                
                '.Send
            End With
        End If
    Next r
    
     'Release Outlook
    Set oMail = Nothing
    Set oApp = Nothing
    
End Sub