UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Vbl URL Link, Office 2013    
 
   
tpitman
post Apr 18 2019, 08:16 AM
Post#1



Posts: 615
Joined: 20-June 04
From: UK


Hi

I have some VBA code which sends an email when the file is closed.


CODE
[/code]
Set newmsg = OutlookApp.CreateItem(olMailItem)
'add recipients
'newmsg.Recipients.Add ("Name Here")
newmsg.Recipients.Add ("email Here")
'add subject
newmsg.Subject = "Changes made to CER Spreadsheet"
'add body
newmsg.Body = "See the changes in the Excel Document ""Controlled Environment Space"": Click here"

[code]


I would like to add a hyperlink when the email recipient clicks on "Click Here"

How do I do this please?


(yes I am aware of the typo in the Topic heading) :-)
This post has been edited by tpitman: Apr 18 2019, 08:18 AM
Go to the top of the page
 
DanielPineault
post Apr 18 2019, 08:39 AM
Post#2


UtterAccess VIP
Posts: 6,660
Joined: 30-June 11



You need to switch from using .Body to .HTMLBody and then you can use standard HTML tags to customize your message
something like:
CODE
newmsg.HTMLBody = "See the changes in the Excel Document ""Controlled Environment Space:"" <a href=""http://www.yoururl.com"">Click here</a>"


Why not use a reusable function such as: http://www.devhut.net/2010/09/03/vba-send-...ook-automation/

--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
tpitman
post Apr 18 2019, 08:41 AM
Post#3



Posts: 615
Joined: 20-June 04
From: UK


Ah! Many thanks..
Go to the top of the page
 
tpitman
post Apr 18 2019, 09:34 AM
Post#4



Posts: 615
Joined: 20-June 04
From: UK


I now have...

CODE
[/code]
.HTMLBody = "<HTML><BODY>" & strUserName & " made changes " & Now() & vbCrLf & " To see the changes in the Excel Document Controlled Environment Space: <a href=http://www.yoururl.com>Click here</a></BODY></HTML>"
[code]


I would like force a new line at "vbCrLf", but at the moment everything is on one line?
Go to the top of the page
 
DanielPineault
post Apr 18 2019, 09:58 AM
Post#5


UtterAccess VIP
Posts: 6,660
Joined: 30-June 11



in HTML you would use <br>
CODE
.HTMLBody = "<HTML><BODY>" &  strUserName & " made changes " & Now() & "<br>To  see the changes in the Excel Document Controlled Environment Space:  <a href=http://www.yoururl.com>Click  here</a></BODY></HTML>"


--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
tpitman
post Apr 18 2019, 10:43 AM
Post#6



Posts: 615
Joined: 20-June 04
From: UK


thank you!
Go to the top of the page
 
tpitman
post Apr 18 2019, 10:57 AM
Post#7



Posts: 615
Joined: 20-June 04
From: UK


Works fine thank you!!


The code currently runs when a user closes the file, whether or not any changes are made. Is it possible to only execute the only file if any changes are made to the file??
Go to the top of the page
 
DanielPineault
post Apr 18 2019, 11:10 AM
Post#8


UtterAccess VIP
Posts: 6,660
Joined: 30-June 11



What event are you using, can we see your full code?

--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
tpitman
post Apr 18 2019, 11:23 AM
Post#9



Posts: 615
Joined: 20-June 04
From: UK


CODE
[/code]
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim strUserName As String
Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem

strUserName = Application.UserName


    Set olApp = Outlook.Application
    'Create e-mail item
    Set objMail = olApp.CreateItem(olMailItem)

    With objMail
        'add recipients
        .Recipients.Add ("recip1")
        .Recipients.Add ("recip2")
        
        'add subject
        .Subject = "Changes made to CER Spreadsheet"
        
       'Set body format to HTML
       .BodyFormat = olFormatHTML
       .HTMLBody = "<HTML><BODY>" & strUserName & " made changes " & Now() & "<br>To see the changes in the Excel Document Controlled Environment Space: <a href=http://www.yoururl.com>Click here</a></BODY></HTML>"
       .Display
       .Send
    End With
    
    Set olApp = Nothing
    Set objMail = Nothing
    
End Sub
[code]
Go to the top of the page
 
DanielPineault
post Apr 18 2019, 01:01 PM
Post#10


UtterAccess VIP
Posts: 6,660
Joined: 30-June 11



You'd probably need to create a global variable and set it's value using the Worksheet Change event and then check it's value BeforeCloseevent to run the code or not.

--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
DanielPineault
post Apr 18 2019, 01:07 PM
Post#11


UtterAccess VIP
Posts: 6,660
Joined: 30-June 11



Look at the attached proof of concept demo I quickly put together.
Attached File(s)
Attached File  DetectChangesDemo.zip ( 12.45K )Number of downloads: 6
 

--------------------
Daniel Pineault (2010-2018 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
tpitman
post Apr 18 2019, 03:52 PM
Post#12



Posts: 615
Joined: 20-June 04
From: UK


Many thanks.

I'll give it a go
Go to the top of the page
 
tpitman
post Apr 19 2019, 01:58 AM
Post#13



Posts: 615
Joined: 20-June 04
From: UK


Works a treat!

Thank You!!
This post has been edited by tpitman: Apr 19 2019, 02:48 AM
Go to the top of the page
 
Debaser
post Apr 25 2019, 12:58 AM
Post#14



Posts: 121
Joined: 11-October 18



You could check Thisworkbook.saved - if it's true, the workbook hasn't been changed.
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    19th June 2019 - 12:37 PM