Sending an Email containing a link to access

[kdbailey]

I have somewhat of an extensive request.

I have a system automated that, when our warranty team receives a warranty part that somebody else has requested information for, will email that person letting them know it has been received.

I want to include a hyperlink in the email that points to the database, opens it with 2 argument containing 2 variables in which one of the forms can auto populate and the information be shown immediately.

So first, I would like to know how to create a hyperlink within the code that sends the email. Second, I would like to know how have the database check for arguments upon opening. I can do the rest.

For instance, John Smith requested part number 123456 on claim number 55555, serial number 7777777. The database is located at "S:\Shared\Warranty Returns\Warranty Returns.accdb". Therefore, the hyperlink would aim at the location and carry the arguments 55555 and 7777777.

Current emailing code (obviously variables are passed into the function):

Function sendemail(msg As String, subj As String, recipients As String, ccrecipients As String, xclaim As Integer, claims() As Variant, serials() As Variant)Dim rsnote As Recordset


Set cdomsg = CreateObject("CDO.message")


If recipients <> "" Then


    With cdomsg.Configuration.Fields
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/smptserverport") = 587
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
        .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
        .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "xx@xx.com"
        .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxx"
        .Update
    End With
    
    With cdomsg
        .To = recipients
        If ccrecipients <> "" Then .CC = ccrecipients
        .From = "Warranty Returns"
        .Subject = subj
        .TextBody = msg
        .Send
    End With
    
    Set rsnote = CurrentDb.OpenRecordset("Notification Table")
    With rsnote
        For d = 0 To xclaim
            .AddNew
                .Fields("Claim Number") = claims(d)
                .Fields("Serial Number") = serials(d)
                .Fields("Notified") = Format(Now(), "m/d/yyyy")
                .Fields("Users Notified") = recipients & IIf(ccrecipients <> "", ", " & ccrecipients, "")
            .Update
        Next d
        .Close
    End With
    
    Set rsnote = Nothing
    
    Set cdomsg = Nothing


End If


End Function

[June7]

Use HTML code tags to embed a link in email body http://www.w3schools.com/tags/tag_a.asp. Example of link to pdf:

<a href=\\xxx.xxx.xxx.xxx\Crm\Library\Materials Reports\_IndexToGeotechReports.pdf"</a>

However, no idea if a db can be opened this way and passing arguments to database is another matter.

Why does recipient need to open the database? Why don't you just put relevant data in the email body?

[kdbailey]

There is far too much information from multiple different source tables to even bother trying to put it into text. If there was a way to pass arguments, it'd be much simpler due to only needing to plug in the claim/serial numbers.

Would this go under .htlmbody? If so, should I transfer the rest of my message into the .htmlbody?

[kdbailey]

Noticed a couple things immediately after trying this out. When switching to .HTMLBody, the command vbCrLf no longer commands a new line in text. Also, as you suggested, "href" does not link to a database. I'm searching for other possibilities currently.

[June7]

Is the recipient on your network? If not, how are they supposed to open the db at all?

Does Chr(13) & Chr(10) work in place of vbCrLf?

[kdbailey]

Yes, everybody is on the same network. Everybody who could be emailed has access to the database.

The character replacements do not work unfortunately within the HTML Body.

As far as getting an actual hyperlink to show, no dice.

Current html statement:

"<a href=" & Chr(34) & "C:\Program Files (x86)\Microsoft Office 2007\Office12\MSACCESS.EXE" & Chr(34) & " " & Chr(34) & "S:\Shared\Warranty Returns\Warranty Returns.accdb" & Chr(34) & ">Open Database</a>"

[June7]

I am not getting the HTML link to work either. Although I have vague recollection of doing this with Access 2007 and Windows XP.

Maybe the HTML is not needed.

Review: https://www.accessforums.net/access/...-vba-6719.html

EDIT: Finally got the syntax right. This worked:
.HTMLBody = "Link to file <a href=""R:\Crm\Library\Materials Reports\_IndexToGeotechReports.pdf""> test.</a>"

Try:

"<a href=""C:\Program Files (x86)\Microsoft Office 2007\Office12\MSACCESS.EXE"" ""S:\Shared\Warranty Returns\Warranty Returns.accdb"">Open Database</a>"

[kdbailey]

Still no actual link to click on, the email reads "Open Database", but it is not clickable. I'm going to test an executable file that will open up the database, see if I can link to that.

[kdbailey]

Still not showing up as a link... For some reason it's basically refusing to embed =/

[kdbailey]

Update:
I am trying to get the hyperlink to simply open a local file. I have scoured google and it seems as if all syntax just does not get the file location to embed. The file is an .exe extension. I've even tried to create a shortcut to the .exe and link to the shortcut... nothing.

Options tried...

msg = "<a href=""File:\\\S:\Shared\Quality\PQG\Kris\Batch Files\EmailOpenReturns.ink"">Open Database</a>"
msg = "<a href=""File:///S:\Shared\Quality\PQG\Kris\Batch Files\EmailOpenReturns.ink"">Open Database</a>"
msg = "<a href=""File:\\\S:\Shared\Quality\PQG\Kris\Batch Files\EmailOpenReturns.exe"">Open Database</a>"

msg = "<a href=""S:\Shared\Quality\PQG\Kris\Batch Files\EmailOpenReturns.ink"">Open Database</a>"
msg = "<a href=""S:\Shared\Quality\PQG\Kris\Batch Files\EmailOpenReturns.exe"">Open Database</a>"

[June7]

This much worked for me:

.HTMLBody = "Link to file <a href=""C:\Temp\Sample.accdb""> test.</a>"

Clicking the link in the email caused a warning popup: "You should only open attachments from a trustworthy source..."

I click Open then the db opens.

No warning with the PDF link.

[kdbailey]

Will this work with chrome?

[kdbailey]

Whatever the issue is, I just cannot get this link to embed. Neither one of the "msg" declarations work...

Function testemail()

Set cdomsg = CreateObject("CDO.message")


With cdomsg.Configuration.Fields
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com"
    .Item("http://schemas.microsoft.com/cdo/configuration/smptserverport") = 587
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
    .Item("http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout") = 60
    .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "xxxx@gmail.com"
    .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "xxxx"
    .Update
End With


msg = "<a href=""S:\Shared\Quality\PQG\Kris\Batch Files\EmailOpenReturns.exe""> Open Database</a>"

'msg = "<a href=""S:\Shared\Warranty Returns\Warranty Returns.accdb""> Open Database</a>"

With cdomsg
    .To = "xxx@xx.com"
    .From = "xx@gmail.com"
    .Subject = "Test"
    .HTMLBody = msg
    .Send
End With


End Function

[June7]

You mean Gmail? It should. Bing: VBA Gmail HTML body
http://www.learnexcelmacro.com/wp/20...mail-or-yahoo/

[kdbailey]

Well, I've read numerous times that Google Chrome is much stricter on what can be done with hyperlinks. And yes, these emails would be opened with GMail.