Command Button or Macro that will email a spreasheet

[Kris]

I was looking for some code that would allow users to click on a button that
would email a spreadsheet after they inputted their information.

What I was hoping for would be the user would have a particular cell where
they would enter an email address or perhaps select an email address from a
drop down box, then they would hit Submit and the file would attach and the
name they entered would already be populated in the TO box. Also, would it
be possible to do a CC or have the subject of the email already entered.

 

[Ron de Bruin]

Hi Kris

Look for the Outlook object mode examples on this page
http://www.rondebruin.nl/sendmail.htm
Click on the Tip link that you find on each example page

 

[Kris]

I am trying to use this part of your site.....

Or you can use a mail address in a cell like this

.To = ThisWorkbook.Sheets("Sheet1").Range("C1").Value

However, the email address in c1 is not emailed. Here is what I have behind
the command button. The cc address does receive the email.

'Working in 2000-2007
'This example send the last saved version of the Activeworkbook
Dim OutApp As Object
Dim OutMail As Object

Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
Set OutMail = OutApp.CreateItem(0)

On Error Resume Next
With OutMail
.To = ThisWorkbook.Sheets("Sheet1").Range("c1").Value
.CC = "(e-mail address removed)"
.BCC = ""
.Subject = "Timesheet Submission"
.Body = "I certify this timesheet is correct. I am forwarding this
to you for management approval."
.Attachments.Add ActiveWorkbook.FullName
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'or use .Display
End With
On Error GoTo 0

Set OutMail = Nothing
Set OutApp = Nothing

End Sub

 

[Ron de Bruin]

Hi Kris

Is your sheet names "Sheet1"
Do you want to send the workbook where the code is

 

[Kris]

yes, I would like to send the entire workbook and not just a particular sheet.

 

[Ron de Bruin]

.To = ThisWorkbook.Sheets("Sheet1").Range("C1").Value

Is the workbook with the macro also the workbook that you want to send ?

Is the mail address in a sheet named "Sheet1" of that workbook ?

 

[Kris]

okay, you were correct. I had to change the Sheet 1 address. Would it be
possible to have the user hit a drop down box and pick a particular email
address. The submit button would then read what is in the drop down box and
email that person?

 

[Ron de Bruin]

Why not use the addressbook

Use Display here

And use the address book

 

[Kris]

That works. .Display. Thank you so much for all your help.

 

[Kris]

I am coming back to this issue. It is possible to use the script to
automatically email the person in the combo box and cc another email address?
The cc address would always be the same, but the name in the combo box would
automatically change.

I am currently using the .display switch and the users are selecting the
email address they want, but I would rather have the ability to email based
on the address they choose in the drop down.

 

[Ron de Bruin]

You can use this

..To = MyComboBox.value

 

[Kris]

I hope you are still out there. I am using your code to submit spreadsheets
and it is working great. I am running into a small issue.

If a person submit a timesheet and the recipent opens it up, if they try to
sumbit it to someone else, they get an error.

I think it has something to do with saving it as a temporary file in the
macro. However, I know that is a vital part to the process.

So my question is how do I fix it to where a user can sumbit the
spreadsheet, a different person pull it up from their email, and then they
can submit it as well?

 

[Ron de Bruin]

If they try to run the code in the workbook without
copying it to the computer they have a problem

Never open and work with files in a mail

 

[Kris]

That's the problem. Basically what we are doing is an employee fills out
their timesheet. They pick an email address in the drop down box. The hit
the submit button. It emails it to the person in the dropdown box.

The person receives the email. They open up the excel file and type in a
box whether they approve or disapprove of the time card.

When they hit submit, they get an error.

What I need is for the approvers to hit submit and it email to payroll so
they can be paid.

What about a second submit button for approvers only? Can you think of a
workaround of some sort?