Trap #Func! error on email hyperlink (1 Viewer)

[mib1019]

Hello all! Long time no post. Hope you are safe and well.

I have a hyperlink field for the email address in my contacts table. On my Insertion Order form, the email address is used for a command button procedure to 1) save the Insertion Order to pdf and 2) attach that PDF to a new Outlook email filled out completely with all the relevant data

The procedure has been running quite well for some time. Recently I'm getting an 'Invalid procedure call or argument' on many of them. Some of the time, the email address has been hard entered, other times copied and pasted from a spreadsheet.

Here is the code:

'Build email variables
strTo = Me.txtEmailAddress
Debug.Print "Email to " & strTo
strTo = Left(strTo, InStr(strTo, "#mailto") - 1) THIS IS WHERE THE ERROR HAPPENS
Debug.Print "Email to " & strTo

I ran a query on the contacts table to determine the problem, adding a field txtEmail with the formula Left(,Instr([Email], "#mailto")-1), and many of the records returned give me the #Func! error.

Examining the hyperlink on a problem record, the address is referencing the spreadsheet cell where the copy/paste has been done.

So I can see that the hyperlink has a problem and so the Left() function is hanging up. I just don't know how to trap this error. Perhaps it should be handled when the user initially enters the email address on the contact's record.

Any advice would be greatly appreciated, as always!

MIB1019

 

[theDBguy]

Hi. I think at the base of the issue is when there is no "#mailto" in the Email data. If so, the InStr() function returns a zero (0). You then subtract 1 to that value and get -1, which you ask the Left() function to use, which in turn, I think, causes the error.