VBA Help with Error Checking

[DatCrazyMongoose]

This is my first post, but I've been coming here for Excel help for a while. This is such a great resource, and I appreciate it very much! Anyway, on to the business at hand... My first VBA script is an attempt to send reminder emails based on cells in a sheet and content from a document. It works, but I know it's not elegant, and I'm afraid I'll have unforeseen problems with errors. I don't know anything about error-checking, so that's what I'd like some advice on as it pertains to this script. Any and all input is appreciated. Thanks!

Sub Send_Reminder_Emails()
'Sends emails using Excel data for "Subject" and "To", and Word document for "Body"

    'Establish variables
    Dim olApp As Object
    Dim olEmail As Object
    Dim wd As Object
    Dim editor As Object
    Dim doc As Object
    Dim row_number As Integer
    row_number = 2
    
    'Open Outlook, Word, and document
    Set olApp = CreateObject("Outlook.Application")
    Set wd = CreateObject("Word.Application")
    Set doc = wd.Documents.Open("P:\BDST\Sales Account Listing\Review Dates\Templates\45Days.docx")

    'Start loop if cell "A'row_number'" is not empty
    Do While ActiveSheet.Range("A" & row_number) > ""
        DoEvents
        
        'Copy contents of Word document
        doc.Content.Copy

        'Create, fill, and send email object
        Set olEmail = olApp.CreateItem(0)
            With olEmail
            .Display
            .To = ActiveSheet.Range("B" & row_number)
            .Subject = ActiveSheet.Range("A" & row_number)
            Set editor = .GetInspector.WordEditor
            editor.Content.Paste
            .Send
            End With

        row_number = row_number + 1
    Loop

    'Clean up
    Set editor = Nothing
    doc.Close
    wd.Quit
    Set doc = Nothing
    Set wd = Nothing
    Set olEmail = Nothing
    Set olApp = Nothing
    
End Sub

 

[DatCrazyMongoose]

Note: I was having issues with Word staying open in the background, causing the script not to run again after the first time. I think I fixed it, but the 'Clean up section may also have issues.

 

[jasonb75]

The most used error checking method for vba is run the code and see what happens.

Things that I would consider as potential errors for your code would be:-
- Word or Outlook not installed.
- The word document that you're setting to the variable, 'doc' can not be found
- No valid email address in Range("B" & row_number)

It depends how robust you want to make your code, you can spend a lot of time allowing for errors that are highly unlikely to occur.

Always use copies for testing so as not to risk loss of important data.

Save a copy of your file (or at least your code) before making any changes to it so that you have something to go back to if the changes don't work.

I notice that your code involves sending mail, for this I would suggest setting up your test documents to send to yourself only rather than unintentionally filling your boss' inbox

Apart from closing word, you shouldn't actually need any of your cleanup section, although it is doing no harm. Your variables are all procedure level, none are static so they should all revert to default value of 'Nothing' when the procedure ends anyway.

 

[DatCrazyMongoose]

Thanks for the tips, Jason. I've added the following to check that the document exists:

 Dim docName As String
    docName = "P:\BDST\Sales Account Listing\Review Dates\Templates\45Days.docx"
    If Dir(docName) = "" Then
        MsgBox "Body template file " & docName & " not found. Check path and try again."
        Exit Sub
    End If

As for valid email addresses, the data in my sheet is already validated so I'm not worried about that. I can handle it manually if it does come up.

Thanks again!