Question emailing with sendobject

[linskill]

Hi All
I have been lurking for a while now and this forum has helped me enormously in getting to grips with access. Many thanks to all of you contributors.
Normally I can find the answers to any queries I have by searching this forum, in this case I am probably not asking the right question so I am hoping that someone can point me in the right direction.
I am using a command button to email an invoice to a customer using the sendobject function, whilst this almost works correctly it is adding an additional mailto:address along with the correct email address.
Example:
CorrectEmailAddress and then#mailto:CorrectEmailAddress

The code I am using is:

On Error Resume Next

Dim Cusemail As String

Cusemail = Nz(DLookup("Email", "invoiceemailQ", "orderID=" & OrderID), "")
If Cusemail = "" Then
MsgBox "No email Address"
Exit Sub
End If

Me.Refresh
DoCmd.SendObject acSendReport, "invoiceemailRpt", acFormatPDF, Cusemail, , , " Subject text", "Message Text"

If hasbeenemailed = False Then
hasbeenemailed = True
Else
hasbeenemailed = False
End If

If hasbeenemailed = True Then
hasbeenemailed = True
End If

I am still very new at this VB stuff and I am scratching my head on this one,
Can anyone see what I am doing wrong. Thanks for any help you can give.
Mark

 

[linskill]

Hi All
Judging by the lack of replies am I right to assume that no one has any ideas or is my question to vague.
I do hope it the latter or this glitch is going to annoy me far a very long time.

 

[NeutronFlux]

Try putting in the line:

Debug.print Cusemail

after giving it a value, for example after your Cusemail = Nz(Dlookup...) line. This will print out the value given to Cusemail in the Immediate Window (press Ctrl+G to open the immediate window).

You can also test out SendObject in the Immediate Window by typing in something like:

DoCmd.SendObject acSendReport, "NameOfAnOpenReport", acFormatPDF, "Your@Email.Address", ,,"Test Subject", "Test Message",0

Aside from that, what version of Access and Outlook are you using?

 

[linskill]

Thanks for the reply, its helped narrow down the problem to the dlookup function.

Dim Cusemail As String


Cusemail = Nz(DLookup("Email", "invoiceemailQ", "orderID=" & OrderID), "")
Debug.Print Cusemail

It is returning the expected email address but adding an additional #mailto:emailaddress after it.
I have checked the values in the invoiceemailQ and they are correct.
I haven't got a clue where to go from here.
Any suggestions gratefully received.

 

[NeutronFlux]

I think it's unlikely to be your DLookup function. It's possible that there are line breaks, tabs or other things in your database fields that are causing the "#mailto:" to be hidden. Make a copy of your database and try this out:

Dim r as DAO.Recordset
Set r = CurrentDb.OpenRecordset("SELECT * FROM invoiceemailQ")

' Loop through all records in table [invoiceemailQ]
If Not (r.EOF And r.BOF) Then
    Do Until r.EOF = True
        r.Edit
        If Not IsNull(r!Email) Then
            ' Get rid of "#mailto:" and empty spaces
            r!Email = Trim(Replace(r!Email, "#mailto:", vbNullString)
            ' Get rid of line breaks and carriage returns
            r!Email = Replace(r!Email, Chr(13), vbNullString)
            r!Email = Replace(r!Email, Chr(10), vbNullString)
            r.Update
       End If
       r.MoveNext
    Loop
End If

Put this code either in a command button or a function or whatever you want and run it once. Then, test your DLookup again.

 

[linskill]

Thanks for the advice, that got rid of the #mailto: unfortunately the problem appears to be with the table. I exported it to excel and all of the email addresses are showing the #mailto:email address concatenated onto main address.
I am wondering if access 2010 hyperlink field type has any thing to do with it as a backup made before I started this major upgrade is fine but that was set as text.
Once again many thanks for you help its gratefully appreciated.

I have just tested it, if I change the data type back to text it causes the above symptoms, anyone know of a way to turn hyperlink data back to text without manually cleaning it up. Grrrrrrr

 

[linskill]

Solved it thanks to this forum.
I cant post the link to the relevant post as I am still a newbie.

Many thanks to you all.