CODEWITHSUNDEEP
vbaexcelemail
Armitage2k
Armitage2k

Reputation: 1264

Excel VBA - application caller range cannot be defined

I have a list with columns containing names and email addresses followed by another column with a "Send Email" button to launch the macro below.

The macro is supposed to pull the location of the current row via the application.caller function and then combine the row number with pre-defined column numbers to success pull first names, last names, email addresses prior populating my email elements. This doesn't seem to work anymore as application.caller returns empty, hence when pressing the send email button no cell/row information are passed on.

Code below:

Sub Mail_ThankYouNote()
'Working in 2000-2010
    Dim OutApp As Object
    Dim outMail As Object
    Dim rng As Range
    Dim lastrow, nationality, SentDate, EmailCount, Title, MailTO, LangStatus As Range

    With Application
        .ScreenUpdating = False
        .EnableEvents = False
    End With

    ActiveWorkbook.Sheets("Data").Select

' check if outlook is open, if not launch application
    Call CheckOutlook

' define column locations for nationality, sent-date, email count, title
    Set OutApp = CreateObject("Outlook.Application")
    Set outMail = OutApp.CreateItem(0)
    Set caller_button = ActiveSheet.Cells(ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row, 14)
    Set nationality = ActiveSheet.Cells(ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row, 7)
    Set SentDate = ActiveSheet.Cells(ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row, 13)
    Set EmailCount = ActiveSheet.Cells(ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row, 15)
    Set Title = ActiveSheet.Cells(ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row, 8)
    Set MailTO = ActiveSheet.Cells(ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row, 12)

    MsgBox caller_button, vbOKOnly, "Info"

' check if title is empty
    If Title = "" Then
        MsgBox "The guest title is empty, please fill in a title before sending the post stay email.", vbOKOnly, "No title"
        Exit Sub
    End If

Really not sure what is causing this as it was working before ...

Appreciate any help on that. A2k

EDIT Turns out this was a problem related to the language difference of my 2 office versions. English copy worked fine, but the Chinese copy required me to delete the buttons and recreate them. As far as I can gather this is due to the fact the English version identifies the shape as (Rectangle Shape XXX) while the Chinese is identifying it as something with UTF 8 characters.

Deleting the shape and recreating the button did the trick.

Upvotes: 0

Views: 969

Answers (1)

Raunak Thomas
Raunak Thomas

Reputation: 1403

Are you sure the application.caller is returning empty? because that seems very unlikely. In the beginning of the program type 

debug.print application.caller

And click on the button, don't run it using F8 or F5. You should be able to see the value in the immediate window. Plus I've faced weird issues while using ActiveX buttons. Try changing it to a normal button. Other that that its difficult to say why this issue is occurring unless we have a look at the workbook itself.

Upvotes: 2

Related Questions