link to file in email using vba (1 Viewer)

murray83

Games Collector
Local time
Today, 11:35
Joined
Mar 31, 2017
Messages
728
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

Enthusiastic Amateur
Local time
Today, 11:35
Joined
Sep 21, 2011
Messages
14,038
I would have thought you would need to use .HTMLbody ?
 

murray83

Games Collector
Local time
Today, 11:35
Joined
Mar 31, 2017
Messages
728
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

Enthusiastic Amateur
Local time
Today, 11:35
Joined
Sep 21, 2011
Messages
14,038
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

Games Collector
Local time
Today, 11:35
Joined
Mar 31, 2017
Messages
728
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 :cool:
 

Users who are viewing this thread

Top Bottom