Opening new email from command button

poetrysue

New Member
Joined
Aug 14, 2019
Messages
4
I am creating a userform that my sales people will use to generate a Purchase Order Request. The form will populate a spreadsheet that my inventory control department can use to track the status of requests. I want the "save" button that saves the userform and populates the data to the spreadhseet to also open a new email (we all use outlook). The email would be from them to our Orders email inbox. This will allow them to attach a file like a quote or a spec sheet to the email.

I'd like the email to have the subject line "PO Request for *" Where * is the value of an ID number Generated by the table in the spreadsheet.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
Welcome to the forum.

You need something like this:

Code:
Sub email_test()
  Dim dam As Object
  ActiveSheet.Copy
  ActiveWorkbook.SaveAs ThisWorkbook.Path & "\PO.xlsx"
  ActiveWorkbook.Close
  Set dam = CreateObject("Outlook.Application").CreateItem(0)
  dam.To = "email@gmail.com"              'recipients
  dam.Subject = "PO Request for " & Range("E2").Value         'Subject
  dam.Body = "body"
  dam.Attachments.Add ThisWorkbook.Path & "\PO.xlsx"   'file
  dam.Display                             ' Change Send to send email
End Sub
 
Upvote 0
Is it possible to change "Outlook.Application" to something like "DefaultEmail.Application" to open up whatever default email program is they are using?
 
Upvote 0
Is it possible to change "Outlook.Application" to something like "DefaultEmail.Application" to open up whatever default email program is they are using?


did you try the macro?

I put outlook for this:

"we all use outlook"
 
Upvote 0
Yes and it works great! And yes we do all use outlook, but the boss wants it to be a little more flexible in case someone is accessing it from a computer that is not their work computer and needs to use a different email client.
 
Upvote 0
I don't know the instruction to have that flexibility.
You could create a new thread, to see if they answer that question.


Meanwhile, it was a pleasure helping you. Good luck
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top