Excel VBA Email confirmation on save with hyperlink to workbook

**cableguy2004** · 02-09-2012, 05:15 AM

I'm a newbie and I'm stuck. I was trying to piece together code from the internet but it's not working. I have an Excel sheet that is shared on a network drive. When it is updated, I need an email sent out that includes a hyperlink back to the sheet that was changed. I tried putting the link in the Excel sheet itself but it appears as text in the email body. Sorry for the repost. The web page timed out and I screwed up the first one. Thanks again!

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
 Cancel As Boolean)
 
'open outlook type stuff
 Set OutlookApp = CreateObject("Outlook.Application")
 Set OlObjects = OutlookApp.GetNamespace("MAPI")
 Set newmsg = OutlookApp.CreateItem(olMailItem)
 'add recipients
 'newmsg.Recipients.Add ("Joe")
 newmsg.Recipients.Add ("joe@aol.com")
 'add subject
 newmsg.Subject = " Leakage"
 'add body
 newmsg.HTMLBody = Range("A1").Value
 newmsg.Display 'display
 newmsg.Send 'send message
 'give conformation of sent message
 MsgBox " Confirmation email has been sent", , "Confirmation"
 





'save the document
 'Me.Worksheets.Save
 
End Sub

Any help is greatly appreciated!

Tony C

**john55** · 02-09-2012, 06:21 AM

hi, try this and see if it works for you

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Set ol = CreateObject("Outlook.application")
Set NewMessage = ol.CreateItem(olMailItem)

With NewMessage
    .To = "xxx@yyy.com"
    .Subject = "This is only a test!"
    .cc = "qqq@www.com"
    'Insert body and Hyperlink below...
    

    .body = "Hi there Mr. John:" & vbNewLine & vbNewLine & "Please click on the link below..." & vbNewLine & vbNewLine & _
              "file:c:\users\john\desktop\teeeest.xls"  & vbNewLine & vbNewLine
    .display
End With

    Set OutMail = Nothing
    Set OutApp = Nothing
End Sub

**cableguy2004** · 02-09-2012, 12:46 PM

John,

Thanks for your help. I have many sheets that will be made and changed frequently. Thats why I was hoping to pull the location of the sheet from a cell range on the sheet itself. The flow would be: Create sheet with hyperlink in A1 with sheets network location. Sheet gets updated and closed. Email is auto generated and sent with link to sheets location in body. Recipient opens email, clicks link and opens sheet with updated info. Once I send this out, people are not going to be able to edit the sheet address in VB. Thats why I need it to read it from a cell in the sheet.

Thanks Again!!

Tony C.

Thread: Excel VBA Email confirmation on save with hyperlink to workbook

Thread Tools

Display

Excel VBA Email confirmation on save with hyperlink to workbook

Re: Excel VBA Email confirmation on save with hyperlink to workbook

Re: Excel VBA Email confirmation on save with hyperlink to workbook

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions