Hello all,
I spent some time today researching on the board and piecing code together to create this macro. I hope this isn't a repost....
I tried to create a macro that would prompt the user for part of a filename, and then save a copy of the file on the user's desktop with the input inserted into the name. Then, it attach the current file to an email in outlook with a specific sent to address, subject, and body, and leave the email up for display.
This is the code I have:
Filename = InputBox("Deal name Please")
ThisWorkbook.SaveAs (Environ("userprofile") & Application.PathSeparator & "Desktop" & Application.PathSeparator & "CLOSED DEAL_" & Filename)
Dim myOutlook As Object
Dim myMailItem As Object
Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
fName = ActiveWorkbook.Path & "" & ActiveWorkbook.Name
With otlNewMail
.To = "me@me.com"
.Subject = "Closed Deal File for " & fName
.Body = "Attached is the File for " & fName
.Attachments.Add fName
.Display
End With
Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing
End Sub
However, I get an error saying that it cannot find the file. Basically, I just want the user to be able to save the file wherever/however they want, and then attached that file to an email with a specific to/subject/body, but with my limited knowledge, this is what I pieced together.
FYI- every user is profiled on their computer. We use outlook
I would appreciate any help I can get. This board has been a lifesaver. Please forgive me for my lack of knowledge!

I spent some time today researching on the board and piecing code together to create this macro. I hope this isn't a repost....
I tried to create a macro that would prompt the user for part of a filename, and then save a copy of the file on the user's desktop with the input inserted into the name. Then, it attach the current file to an email in outlook with a specific sent to address, subject, and body, and leave the email up for display.
This is the code I have:
Filename = InputBox("Deal name Please")
ThisWorkbook.SaveAs (Environ("userprofile") & Application.PathSeparator & "Desktop" & Application.PathSeparator & "CLOSED DEAL_" & Filename)
Dim myOutlook As Object
Dim myMailItem As Object
Set otlApp = CreateObject("Outlook.Application")
Set otlNewMail = otlApp.CreateItem(olMailItem)
fName = ActiveWorkbook.Path & "" & ActiveWorkbook.Name
With otlNewMail
.To = "me@me.com"
.Subject = "Closed Deal File for " & fName
.Body = "Attached is the File for " & fName
.Attachments.Add fName
.Display
End With
Set otlNewMail = Nothing
Set otlApp = Nothing
Set otlAttach = Nothing
Set otlMess = Nothing
Set otlNSpace = Nothing
End Sub
However, I get an error saying that it cannot find the file. Basically, I just want the user to be able to save the file wherever/however they want, and then attached that file to an email with a specific to/subject/body, but with my limited knowledge, this is what I pieced together.
FYI- every user is profiled on their computer. We use outlook
I would appreciate any help I can get. This board has been a lifesaver. Please forgive me for my lack of knowledge!
