Email Trigger

[vmjan02]

This code is all perfect, just need to add on the body of the email the path of the updated or lasted folder created.

so in Fpath the other scrip creates a folder when ever it is run (screen shot for ref),

In body of the email will be "Hi All updated report is kept in folder " and Fpaht and folder name eg:- "31-Oct-23 Time 10-00"

Sub EmailTigger()

Dim Fpath As String
Fpath = ActiveWorkbook.Path

Sheets("Dashboard").Select
    'Open a new mail item
    Dim outlookApp As Outlook.Application
    Set outlookApp = CreateObject("Outlook.Application")
    Dim outMail As Outlook.MailItem
    Set outMail = outlookApp.CreateItem(olMailItem)
    'Get its Word editor
    outMail.Display
   
   With outMail
    .Body = ""
'    .Body = Sheets("Email").Range("C7").Value
    .To = Sheets("Email").Range("C2").Value
    .CC = Sheets("Email").Range("C3").Value
    .BCC = Sheets("Email").Range("C4").Value
    .Subject = Sheets("Email").Range("C5").Value
    
    .Display
   End With

[ATTACH type="full"]101239[/ATTACH]
   
    Dim wordDoc As Word.document
    Set wordDoc = outMail.GetInspector.WordEditor
    ' Copy and paste ranges from desired sheets
    PastePic wordDoc, "Dashboard!A1:X63"
Sheets("Dashboard").Select
End Sub


Private Sub PastePic(wordDoc As Word.document, rngRange As String)
    Dim r As Word.Range
    'Copy range of interest
    Range(rngRange).Copy
    'Paste as picture in sheet and cut immediately
    ActiveSheet.Pictures.Paste.Cut
    Set r = wordDoc.Content
    r.collapse Direction:=wdCollapseEnd
    r.Paste
    i = wordDoc.InlineShapes.Count
    wordDoc.InlineShapes.Item(i).ScaleHeight = 85
    'wordDoc.InlineShapes.Item(I).ScaleWidth = 100
End Sub

 

[Micron]

What calls the email code, the procedure that creates the folder or file? Then have that pass the path to the called procedure.

EmailTrigger FldrName << a variable that contains the folder string you want in the email
**end of first procedure here**

Sub EmailTigger(FldrName)
**email code here**
in the .Body, add your text:
"Hi All updated report is kept in folder " & Fpaht & "/" & FldrName

Your body will have to be .HTML format and if you want that path to be a link you will probably need to use html codes to make it a hyperlink. Whether or not that makes sense depends on any network security that will or won't allow email links to be followed.

 

[vmjan02]

What calls the email code, the procedure that creates the folder or file? Then have that pass the path to the called procedure.

EmailTrigger FldrName << a variable that contains the folder string you want in the email
**end of first procedure here**

Sub EmailTigger(FldrName)
**email code here**
in the .Body, add your text:
"Hi All updated report is kept in folder " & Fpaht & "/" & FldrName

Your body will have to be .HTML format and if you want that path to be a link you will probably need to use html codes to make it a hyperlink. Whether or not that makes sense depends on any network security that will or won't allow email links to be followed.

ok will give it a try