Emailing from html link

[DMT Dave]

Hi Guy's, I have used the the following code so I can find part of a mobile number, then on each line where i have "Send Email / rs.Fields("Email") / Click Here



When i hover over the link, the correct email displays on the tag from hovering but it wont generate and email ?

Do i need to add code for a new email within the HRef const Q ?

Private Sub txtMobile_DblClick(Cancel As Integer)Const Q = """"
Dim myItem As Outlook.MailItem
Dim OutAccount As Outlook.Account
Dim myApp As New Outlook.Application
Dim BoxStart As String, BoxEnd As String, FontStart As String, FontEnd As String, rsBody As String, rsEmail As String, strMail As String
Dim strA As String, strB As String, strC As String, strD As String, strE As String, strF As String, strG As String, strH As String, strI As String, SigFile As String
Dim strOpt As String
Dim rs As DAO.Recordset
Dim iOpt As Integer




BoxStart = "<table style='text-align:left;border:3px solid black;font-family:calibri;border-collapse:collapse;padding:25px'><tr style='background:white;mso-highlight:blue'>"
BoxEnd = "</tr></table>"
FontStart = "<font size='3'>"
FontEnd = "</font>"
strButton = "<tr><td><button class='editbtn'>edit</button></td></tr>"


strOpt = InputBox("Enter Any Part Of The Mobile Number To Search ?" & vbNewLine & vbNewLine & _
"ie: You Have Double Clicked On " & Me.Mobile & vbNewLine & vbNewLine & _
"You Can Type In For Example: 327", "ENTER NUMBERS")


Set rs = CurrentDb.OpenRecordset("Select * From tblRemovals WHERE Mobile Like ""*" & strOpt & "*""")
Do Until rs.EOF
rsBody = rsBody & rs.Fields("Client") & "   " & rs.Fields("Town") & "   " & rs.Fields("PostCode") & "   " & rs.Fields("LiftMake") & "   " & _
rs.Fields("LiftModel") & "   " & rs.Fields("Tel") & "   " & "<B>" & rs.Fields("Mobile") & "</B>" & "|" & _
"&emsp;&emsp;" & "<span style='background:yellow'>" & rs.Fields("RecordNo") & "</span>" & " " & _
Format(rs.Fields("RemovalDate"), "ddd-dd-mmm-yyyy") & "&emsp;&emsp;Send Email:&emsp;&emsp;<a href=" & Q & rs.Fields("Email") & Q & ">" & "Click Here" & "</a>" & "|"
rs.MoveNext
Loop


strA = rs.RecordCount & " Mobile Numbers Found Containing<span style='background:yellow'>" & " " & strOpt & "</span>"


rsEmail = Me.EMail


Set myItem = myApp.CreateItem(olMailItem)
Set OutAccount = myApp.Session.Accounts.Item(2)
    With myItem
    .Subject = "Mobile Numbers Found"
    .To = ""
    .HTMLBody = strA & "<br>" & "<br>" & BoxStart & "<br>" & Replace(rsBody, "|", "<br>" & "<br>")
    .SendUsingAccount = OutAccount
    .Display
    End With


Set myApp = Nothing
Set myItem = Nothing

Click Here does nothing even though the email appears on hover tag

[Welshgasman]

So tired of asking.

Have you walked though the code? Your .To has no email address?

[Micron]

WGM, I interpret as the email gets created and contains the link but the expectation is that clicking on an html hyperlink will somehow interact with vba to do something just with the href attribute. That would be a new one for me. Not only that, the email generation is outside of the loop, so if there are 5 records in the recordset, nothing is done regarding email generation until the last loop. In that case, I think your comment about stepping is valid.

[Welshgasman]

Well, all I can say, is when something did not work for me, that is the first thing I used to try?

O/P seems reluctant to do so, every time? Frustrating, really frustrating, to me at least.

[Welshgasman]

I suggest uploading a test db with enough to replicate the problem, and instructions on how to do so?

[DMT Dave]

Hi guy's thank you, i will do debug.print and see what is in the immediate window and work out if and where any sequence i may have is wrong

and the .to wouldn't be relevant, i am not trying send the email that generates, it is purely just to list, i guess i could use a form with relevant fields and set the email field to generate a mail form a textbox

[DMT Dave]

Thanks Micron, the email needs to be outside of the loop as it's just purely to show the result from the recordset, it's the email form the rs.fields in the href i was wondering if i can send

I have used href quite a lot now as a link to web page and various other web address, was just unsure if it can be used to generate an email based on the email in the href

[Welshgasman]

Ok, so you produce an email that has a bunch of links that are email addresses?
You now expect to click one of those links to open an email?
Have you looked at what is required for that to work?, in any email you have received?

I would expect a Mailto: prefix at least.

[DMT Dave]

WGM, yes correct, no i need to look into it more or simplify to a form but your right as in the method

I think i would need the link to generate a new email when clicked by perhaps setting a new outlook application, do you think this is way complicated and change to using a form for this option ?

[Welshgasman]

You do not need to set a new mail application.
If you construct the link correctly, the default mail application will open?

Hover over the email links at the bottom of https://www.bibby-gazette.co.uk/ to see what is required.

Now click one one of them.

No need for a basic email to hold the links in the first place, I would have thought?, a form would work just as well and be less confusing to the user (and me )

[DMT Dave]

Yes, i think i might just open a form when double clicked on Mobile field and populate a list box, that would be easier, then the only email i would need is the email address on the row number clicked on

Sorry for confusing and thanks for replies

[DMT Dave]

Hi WGM and Micron, I have dug deeper into this and this does actually work, I am further going to be adding the body within the html but my first one that i can't find so far is how to add a specific email account in html tag ?

This does open outlook but not form the correct account, trying to find the method of adding the correct account within html

Set rs = CurrentDb.OpenRecordset("Select * From tblRemovals WHERE Mobile Like ""*" & strOpt & "*"" ORDER BY RemovalDate DESC")Do Until rs.EOF
rsBody = rsBody & rs.Fields("Client") & "   " & rs.Fields("Town") & "   " & rs.Fields("PostCode") & "   " & rs.Fields("LiftMake") & "   " & _
rs.Fields("LiftModel") & "   " & rs.Fields("Tel") & "   " & "<B><font size='4'>" & rs.Fields("Mobile") & "</font></B>" & "|" & _
"&emsp;&emsp;" & "<span style='background:yellow'>" & rs.Fields("RecordNo") & "</span>" & " " & _
Format(rs.Fields("RemovalDate"), "ddd-dd-mmm-yyyy") & "&emsp;&emsp;&emsp;&emsp;" & _
"<a href=mailto:" & rs.Fields("Email") & "?subject=Missed%20Call%20From%20" & rs.Fields("Mobile") & ">" & rs.Fields("Email") & "</a>" & "|" & _
"<hr style='height:2px;border-width:0;color:gray;background-color:gray'>"
rs.MoveNext
Loop

[DMT Dave]

This is trying to send from "mail from windows" as default

[Welshgasman]

So make Outlook your default mail application?

[DMT Dave]

Not sure how to do this within the html

can easily set it outside of the html but i am assuming this needs to be set within html ?