How to capture File Name and Path of Workbook and Automatically Attach to an opened outlook msg

[tbakbradley]

I have a Maco that will open a specific Worksheet. The Save As Dialog Box then appears so the Employee can save the file. Once finished, a two saved .msg Email Templates are opened from a Server that already includes the Address, Subject Line and Body for each Template.

Is there a way, within the macro, to capture the File Name and Path provided by the employee on the active workbook and then attach that file to the two .msg outlook file that opens?

Sub Data_Build()
    Application.EnableEvents = False  
    Sheets("FIM Data").Visible = True
    Sheets("FIM Data").Select
    Range("A1").Select
    MsgBox "Save your File and attach it to the Email Templates that open Automatically", vbExclamation, "Save and Send"
    Application.Dialogs(xlDialogSaveAs).Show
    Application.EnableEvents = True 
    Call OpenFIMOutlookTemplate
    Call OpenJeffOutlookTemplate

Sub OpenFIMOutlookTemplate()


Dim myolapp As Object
Dim myitem As Object


Set myolapp = CreateObject("Outlook.Application")


Set myitem = myolapp.CreateItemFromTemplate("\\Server\Folders\Email to FIM IT.msg")
myitem.Display 


End Sub

Sub OpenJeffOutlookTemplate()


Dim myolapp As Object
Dim myitem As Object


Set myolapp = CreateObject("Outlook.Application")


Set myitem = myolapp.CreateItemFromTemplate("\\Server\Folders\Email to Jeff.msg")
myitem.Display 


End Sub

I'm not sure if this is doable, but would love to see as it would save the Employees time attaching the same file to two different emails (why we can't just sent one to both parties......don't ask )

 

[mole999]

ENVIRON should give you the parts you are after, as for attaching I guess that can happen with the provided detail

 

[tbakbradley]

ENVIRON should give you the parts you are after, as for attaching I guess that can happen with the provided detail

I am truly sorry, but I did not understand that comment. What is ENVRON?

 

[JLGWhiz]

maybe a syntax along this line

With myolapp
 .Attachements.Add ActiveWorkbook.FullName
End With

Although I don't believe it will allow you to attach the host workbook of the code. The arttachment has to be closed at run time. but FullName is what you want to use to include the path and file name.

 

[tbakbradley]

maybe a syntax along this line

With myolapp
 .Attachements.Add ActiveWorkbook.FullName
End With

Although I don't believe it will allow you to attach the host workbook of the code. The arttachment has to be closed at run time. but FullName is what you want to use to include the path and file name.

Thanks. I was looking for the open file to be saved to the Outlook Templates that are opened up. Oh well, it doesn't take a few seconds more for the Employees to attach manually.

 

[tbakbradley]

I was able to Build an Outlook Email through my Macro as a test, but I'm only able to "Send" it.

Is there a way to NOT send it, but just have it Opened on the desktop so the user can add additional verbiage before sending?

I tried

.show

instead of

.send

and I received an error

Sub OutlookEmailTest()Dim outlookApp As Object
Dim OutlookMail As Object


Set outlookApp = CreateObject("Outlook.Application")
Set OutlookMail = outlookApp.CreateItem(0)


With OutlookMail
.To = "david.a.bradley@verizon.com"
.Subject = "Hello, this is a test"
.BodyFormat = 2
.HTMLBody = "Hi, <p>I'm sending this message through VBA"
.Attachments.Add ActiveWorkbook.FullName
.Send
End With


Set OutlookMail = Nothing
Set outlookApp = Nothing


End Sub

 

[tbakbradley]

I was able to Build an Outlook Email through my Macro as a test, but I'm only able to "Send" it.

Is there a way to NOT send it, but just have it Opened on the desktop so the user can add additional verbiage before sending?

I tried

.show

instead of

.send

and I received an error

Sub OutlookEmailTest()Dim outlookApp As Object
Dim OutlookMail As Object


Set outlookApp = CreateObject("Outlook.Application")
Set OutlookMail = outlookApp.CreateItem(0)


With OutlookMail
.To = "david.a.bradley@verizon.com"
.Subject = "Hello, this is a test"
.BodyFormat = 2
.HTMLBody = "Hi, I'm sending this message through VBA"
.Attachments.Add ActiveWorkbook.FullName
.Send
End With


Set OutlookMail = Nothing
Set outlookApp = Nothing


End Sub

All is working now. It's

.display

 

[JLGWhiz]

Just logged back on. Glad you found what you needed.
Regards, JLG