Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-16-2018, 02:21 AM   #1
murray83
sega collector
 
Join Date: Mar 2017
Location: Daventry, UK
Posts: 275
Thanks: 48
Thanked 0 Times in 0 Posts
murray83 is on a distinguished road
link to file in email using vba

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) & " < A href=G:\GENERAL\Databases\Handover\Handover Database.mdb >Link< /A > "
           '" 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

murray83 is offline   Reply With Quote
Old 03-16-2018, 02:31 AM   #2
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 1,794
Thanks: 233
Thanked 257 Times in 242 Posts
Gasman will become famous soon enough Gasman will become famous soon enough
Re: link to file in email using vba

I would have thought you would need to use .HTMLbody ?
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
murray83 (03-16-2018)
Old 03-16-2018, 04:14 AM   #3
murray83
sega collector
 
Join Date: Mar 2017
Location: Daventry, UK
Posts: 275
Thanks: 48
Thanked 0 Times in 0 Posts
murray83 is on a distinguished road
Re: link to file in email using vba

Quote:
Originally Posted by Gasman View Post
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"
        .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>"
        .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 ??

murray83 is offline   Reply With Quote
Old 03-16-2018, 05:03 AM   #4
Gasman
Access newbie
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 1,794
Thanks: 233
Thanked 257 Times in 242 Posts
Gasman will become famous soon enough Gasman will become famous soon enough
Re: link to file in email using vba

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
__________________
Access novice. Sometimes trying to give something back.
Access 2007
Gasman is offline   Reply With Quote
The Following User Says Thank You to Gasman For This Useful Post:
murray83 (03-16-2018)
Old 03-16-2018, 05:27 AM   #5
murray83
sega collector
 
Join Date: Mar 2017
Location: Daventry, UK
Posts: 275
Thanks: 48
Thanked 0 Times in 0 Posts
murray83 is on a distinguished road
Thumbs up Re: link to file in email using vba

Quote:
Originally Posted by Gasman View Post
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

murray83 is offline   Reply With Quote
Reply

Tags
.bodyhtml , email , link

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
insert HtML file in to body of email (inside loop email) GaryPanic Modules & VBA 9 05-06-2015 12:56 AM
Link in email doesn't work if underscores in file name projecttoday General 2 10-23-2014 04:39 AM
Download File from Internet (link on field) and save it(link on field). Ucramos Modules & VBA 2 07-25-2011 04:40 AM
Email link dogman01 Forms 1 02-12-2009 01:23 PM
Email A Link? Moonshine Modules & VBA 1 07-02-2003 05:23 AM




All times are GMT -8. The time now is 04:14 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World