Run time 13 errors on an email notification

[Confused_UK]

Hi all

I'm looking for some guidance. I have done a script to send an email notification when a database is updated.

"EmailTo" is a named range of email addresses
"EmailSubject" is a named cell with the subject header
"EmailBody" is a named cell with the body of the email

Sub EmailNotification()


    Dim Mailaddress As String
    Dim olApp As Object
    
    If Range("EmailTo").Value <> "" Then
        Set olApp = CreateObject("Outlook.Application")
        
        With olApp.CreateItem(0)
            .To = Range("EmailTo").Value
            .Subject = Range("EmailSubject").Text
            .Body = Range("EmailBody").Text
            .Display
            .Send
        End With
    End If
    
End Sub

However, when I run it, I get a type mismatch run time error 13 against the line

If Range("EmailTo").Value <> "" Then

What have I missed? Any help would be greatly appreciated.

 

[RickXL]

Hi,

The address list needs to be a String with Delimiters and not a Range. Try this:

Sub EmailNotification()
    Dim olApp       As Object
    Dim Addresses   As Variant
    Dim EmailTo     As String
    Dim delim       As String
    Dim ele         As Variant
    
    Addresses = Range("EmailTo").Value
    delim = ""
    For Each ele In Addresses
        EmailTo = EmailTo & delim & ele
        delim = ";"
    Next
    
    If EmailTo <> "" Then
        Set olApp = CreateObject("Outlook.Application")
        With olApp.CreateItem(0)
            .To = EmailTo
            .Subject = Range("EmailSubject").Text
            .Body = Range("EmailBody").Text
            .Display
            .Send
        End With
    End If
End Sub

It reads the Range of addresses into a variant then concatenates all the elements using a delimiter.


Regards,

 

[HotRhodium]

I really like working with VBA but you have found the most ill thought out aspect of VBA and Excel the range. Welcome no nonsense land and it gets worse. Using the unscoped "Range" will cause errors when done correctly sometimes and always when not used correctly. First is the named range you are looking for a sheet scoped or workbook scoped range? This may be your issue or not. To avoid this issue you can convert to either

ThisWorkbook.Names("EmailTo").RefersToRange.Value

Or

MySheetAsObject.Names("EmailTo").RefersToRange.Value

This resolves the issue of VBA not having a workbook scoped "Range" because you do have workbook scoped "Name" yay! They also seem to be more stable over older versions of excel just do not try to set non-continuous ranges with them awww snap thought you were in the clear. Now you may also have an issue caused by merged cells if that is the case then edit your range names to only include the top left cell in your group of merged cells or use this so you are only working with the top left cell of your range that has multiple cells.

ThisWorkbook.Names("EmailTo").RefersToRange.Cells(1,1).Value

Or

MySheetAsObject.Names("EmailTo").RefersToRange.Cells(1,1).Value

I consider myself to be reasonably smart but this one makes my eyes go crossed good luck hope that helps

 

[HotRhodium]

Sorry guys I put this on the wrong thred

 

[Confused_UK]

Thank you Rick. That seems to work perfectly.