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.

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

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Kris" <(E-Mail Removed)> wrote in message news:F0F700FA-C4CE-40B8-BAE9-(E-Mail Removed)...
>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.

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 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" wrote:

> 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
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Kris" <(E-Mail Removed)> wrote in message news:F0F700FA-C4CE-40B8-BAE9-(E-Mail Removed)...
> >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.
>

Hi Kris

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


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Kris" <(E-Mail Removed)> wrote in message news:8F3F0EA9-AF6B-44AF-ACB5-(E-Mail Removed)...
>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 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" wrote:
>
>> 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
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "Kris" <(E-Mail Removed)> wrote in message news:F0F700FA-C4CE-40B8-BAE9-(E-Mail Removed)...
>> >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.
>>

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

"Ron de Bruin" wrote:

> Hi Kris
>
> Is your sheet names "Sheet1"
> Do you want to send the workbook where the code is
>
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Kris" <(E-Mail Removed)> wrote in message news:8F3F0EA9-AF6B-44AF-ACB5-(E-Mail Removed)...
> >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 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" wrote:
> >
> >> 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
> >>
> >> --
> >>
> >> Regards Ron de Bruin
> >> http://www.rondebruin.nl/tips.htm
> >>
> >>
> >> "Kris" <(E-Mail Removed)> wrote in message news:F0F700FA-C4CE-40B8-BAE9-(E-Mail Removed)...
> >> >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.
> >>
>

> .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 ?


--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Kris" <(E-Mail Removed)> wrote in message news:31B6B264-3374-4D6E-8E3E-(E-Mail Removed)...
> yes, I would like to send the entire workbook and not just a particular sheet.
>
> "Ron de Bruin" wrote:
>
>> Hi Kris
>>
>> Is your sheet names "Sheet1"
>> Do you want to send the workbook where the code is
>>
>>
>> --
>>
>> Regards Ron de Bruin
>> http://www.rondebruin.nl/tips.htm
>>
>>
>> "Kris" <(E-Mail Removed)> wrote in message news:8F3F0EA9-AF6B-44AF-ACB5-(E-Mail Removed)...
>> >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 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" wrote:
>> >
>> >> 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
>> >>
>> >> --
>> >>
>> >> Regards Ron de Bruin
>> >> http://www.rondebruin.nl/tips.htm
>> >>
>> >>
>> >> "Kris" <(E-Mail Removed)> wrote in message news:F0F700FA-C4CE-40B8-BAE9-(E-Mail Removed)...
>> >> >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.
>> >>
>>

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?

"Kris" wrote:

> yes, I would like to send the entire workbook and not just a particular sheet.
>
> "Ron de Bruin" wrote:
>
> > Hi Kris
> >
> > Is your sheet names "Sheet1"
> > Do you want to send the workbook where the code is
> >
> >
> > --
> >
> > Regards Ron de Bruin
> > http://www.rondebruin.nl/tips.htm
> >
> >
> > "Kris" <(E-Mail Removed)> wrote in message news:8F3F0EA9-AF6B-44AF-ACB5-(E-Mail Removed)...
> > >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 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" wrote:
> > >
> > >> 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
> > >>
> > >> --
> > >>
> > >> Regards Ron de Bruin
> > >> http://www.rondebruin.nl/tips.htm
> > >>
> > >>
> > >> "Kris" <(E-Mail Removed)> wrote in message news:F0F700FA-C4CE-40B8-BAE9-(E-Mail Removed)...
> > >> >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.
> > >>
> >

Why not use the addressbook

>>>> .Send 'or use .Display

Use Display here

And use the address book

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Kris" <(E-Mail Removed)> wrote in message news:9E36D74F-0A1B-4183-9372-(E-Mail Removed)...
> 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?
>
> "Kris" wrote:
>
>> yes, I would like to send the entire workbook and not just a particular sheet.
>>
>> "Ron de Bruin" wrote:
>>
>> > Hi Kris
>> >
>> > Is your sheet names "Sheet1"
>> > Do you want to send the workbook where the code is
>> >
>> >
>> > --
>> >
>> > Regards Ron de Bruin
>> > http://www.rondebruin.nl/tips.htm
>> >
>> >
>> > "Kris" <(E-Mail Removed)> wrote in message news:8F3F0EA9-AF6B-44AF-ACB5-(E-Mail Removed)...
>> > >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 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" wrote:
>> > >
>> > >> 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
>> > >>
>> > >> --
>> > >>
>> > >> Regards Ron de Bruin
>> > >> http://www.rondebruin.nl/tips.htm
>> > >>
>> > >>
>> > >> "Kris" <(E-Mail Removed)> wrote in message news:F0F700FA-C4CE-40B8-BAE9-(E-Mail Removed)...
>> > >> >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.
>> > >>
>> >

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

"Ron de Bruin" wrote:

> Why not use the addressbook
>
> >>>> .Send 'or use .Display
>
> Use Display here
>
> And use the address book
>
> --
>
> Regards Ron de Bruin
> http://www.rondebruin.nl/tips.htm
>
>
> "Kris" <(E-Mail Removed)> wrote in message news:9E36D74F-0A1B-4183-9372-(E-Mail Removed)...
> > 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?
> >
> > "Kris" wrote:
> >
> >> yes, I would like to send the entire workbook and not just a particular sheet.
> >>
> >> "Ron de Bruin" wrote:
> >>
> >> > Hi Kris
> >> >
> >> > Is your sheet names "Sheet1"
> >> > Do you want to send the workbook where the code is
> >> >
> >> >
> >> > --
> >> >
> >> > Regards Ron de Bruin
> >> > http://www.rondebruin.nl/tips.htm
> >> >
> >> >
> >> > "Kris" <(E-Mail Removed)> wrote in message news:8F3F0EA9-AF6B-44AF-ACB5-(E-Mail Removed)...
> >> > >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 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" wrote:
> >> > >
> >> > >> 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
> >> > >>
> >> > >> --
> >> > >>
> >> > >> Regards Ron de Bruin
> >> > >> http://www.rondebruin.nl/tips.htm
> >> > >>
> >> > >>
> >> > >> "Kris" <(E-Mail Removed)> wrote in message news:F0F700FA-C4CE-40B8-BAE9-(E-Mail Removed)...
> >> > >> >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.
> >> > >>
> >> >
>

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.