link to file in email using vba

murray83 · Mar 16, 2018

morning one and all

this is my current code which isnt broken as it does work just not as i would like

Code:

'email script for MTM Report
Public Sub CreateEmailWithOutlookMTM()

'bits for the attachment
Dim myPath As String
Dim strReportName As String
todayDate = Format(Date, "DDMMYY")

    myPath = "G:\GENERAL\Databases\Handover\Reports\MTM\"
     strReportName = "MTM Handover" & " " & todayDate & ".pdf"

' Create a new email object
    Set olApp = CreateObject("Outlook.Application")
    Set olMailItem = olApp.createitem(olMailItem)

    ' Add the To/Subject/Body to the message and display the message
    With olMailItem
        .To = "test@testing.com "
            .cc = ""
        .Subject = "MTM Handover"
        .Body = "Please see Attached MTM Handover" & Chr$(13) & _
             Chr$(13) & "find the database here." & Chr$(13) & _
             Chr$(13) & " [COLOR="Red"]< A href=G:\GENERAL\Databases\Handover\Handover Database.mdb >Link< /A >[/COLOR] "
           '" G:\GENERAL\Databases\Handover\Handover Database.mdb"
           .Attachments.Add myPath & strReportName 'FileName"
        .Display    ' Displays before sending the message
    End With
    
    ' Release all object variables
    Set olMailItem = Nothing
    Set olApp = Nothing
    

End Sub

bit highlighted in red i would wish to appear as a link in outlook ( just if you had done ctrl + k in outlook )

but all it does i put it on screen

Gasman · Mar 16, 2018

I would have thought you would need to use .HTMLbody ?

murray83 · Mar 16, 2018

Gasman said:
I would have thought you would need to use .HTMLbody ?

I have done that, many thanks for the pointer as you can see below, highlighted in green

Code:

'email script for MTM Report
Public Sub CreateEmailWithOutlookMTM()

'bits for the attachment
Dim myPath As String
Dim strReportName As String
todayDate = Format(Date, "DDMMYY")

    myPath = "G:\GENERAL\Databases\Handover\Reports\MTM\"
     strReportName = "MTM Handover" & " " & todayDate & ".pdf"

' Create a new email object
    Set olApp = CreateObject("Outlook.Application")
    Set olMailItem = olApp.createitem(olMailItem)

    ' Add the To/Subject/Body to the message and display the message
    With olMailItem
        .To = "test@testing.com"
            .cc = ""
        .Subject = "MTM Handover"
        [COLOR="Lime"].HTMLBody = "<html><body><font face=calibri>Hi All,<br> Please see the database<a href=#G:\GENERAL\Databases\Handover\Handover Database.mdb#> Here</a></font></body></html>[/COLOR]"
        .Attachments.Add myPath & strReportName 'FileName"
        .Display    ' Displays before sending the message
    End With
    
    ' Release all object variables
    Set olMailItem = Nothing
    Set olApp = Nothing
    

End Sub

just have one last question, the link is displayed in the email but when you click it nothing happens, is that to do with the file path ??

Gasman · Mar 16, 2018

I would expect so. You would need to see what a valid link looks like and compare.
I'm pretty sure the # should not be there?

I've just inserted a li9nk in an email and had the code below

Code:

href="file:///F:\Users\Paul\Documents\DWM%20Client%20Agreement.pdf">F:\Users\Paul\Documents\DWM Client Agreement.pdf</a><o:p></o:p></p><p

murray83 · Mar 16, 2018

Gasman said:
I would expect so. You would need to see what a valid link looks like and compare.
I'm pretty sure the # should not be there?

I've just inserted a li9nk in an email and had the code below

Code:

href="file:///F:\Users\Paul\Documents\DWM%20Client%20Agreement.pdf">F:\Users\Paul\Documents\DWM Client Agreement.pdf</a><o:p></o:p></p><p

quite right worked a charm thanks

link to file in email using vba

murray83

Games Collector

Gasman

Enthusiastic Amateur

murray83

Games Collector

Gasman

Enthusiastic Amateur

murray83

Games Collector

Similar threads

Users who are viewing this thread