Create a Command Button to Send E-Mails to selected recipients

[MeMe]

I have an excel file that I send weekly to some Managers. Currently, I copy
and paste a list of e-mail addresses in a new Outlook message and then send
the file. I wanted to create a Command Button, but I can't create the VBA
code to send the excel file via Outlook. Can I get some help please?

thanks, meme

 

[Ron de Bruin]

Hi MeMe

You find all the code you need on this page
(click also on the Tip page link on each example page0
http://www.rondebruin.nl/sendmail.htm

 

[MeMe]

Hi Ron,

thank you for your link. It helped out a lot. I have another, hopefully
quick, question: can I add an auto signature to this macro? The trick is that
there are multiple signatures that could be chosen and I would want the
signature to appear after the icon.

Lastly, when I type the text for the body, it is to the left of the icon and
I would like to have the intro sentence on the first line saying here is your
weekly file, then the icon underneath the intro, then our department
signature underneath the icon kinda idea.

Opps, one more, what is the best way to protect a macro from being
viewed/changed?

Appreciate any help you can offer.

 

[Ron de Bruin]

See
http://www.rondebruin.nl/mail/folder3/signature.htm

Lastly, when I type the text for the body, it is to the left of the icon

What do you mean with icon
Do you use a old version of Outlook

 

[MeMe]

Hi Ron,

am checking out what you sent me.

Outlook is 2003 and what I mean by icon is the excel file attachement image.
So my comments go to the left of this icon and it is like the text and the
picture is on the same "line" in Outlook.

 

[Gord Dibben]

With your workbook open and VBE project explorer open, right-clcik on your
Project/workbook and "Project Properties>Protection>Lock for viewing and give a
password.

DO NOT forget password.

Save and close the workbook before protection will take place.


Gord Dibben MS Excel MVP

 

[MeMe]

hi Ron,

my Command Button doesn't work anymore. I have to run the macro from the menu.

I can't even right-click to get to format our properties. I don't know what
I did, but I did try to get that signature to work.

Help, please.

 

[Ron de Bruin]

Do you have a formas burron or a control toolbox button on your sheet ?

 

[Ron de Bruin]

Do you say that the attacment icon is in the body and not in a seperate line above you body ?

 

[Ron de Bruin]

Thanks Gord

I missed that one

 

[MeMe]

I have a control toolbox open - not sure about the other? I actually just
deleted the macro and the command button, redid it, and it still wont run. I
did not inlcude the html signature this time. What did I do to cause the
macro to not work anymore?

 

[Ron de Bruin]

If you use a control toolbox button then go into Edit more (first button on the toolbox)
Then double click the button
The VBA editor opens with the click event

Private Sub CommandButton1_Click()

End Sub

You can add you code between the two lines or use

Private Sub CommandButton1_Click()
Call YourMacroName
End Sub

Then close Edit mode and try it again

 

[MeMe]

Hi Ron,

I don't know what I did. I had this working since you first sent the your
set of intructions. I foolishly did not save in a separate file what was
working prior to trying to include a signature. But nothing from today has
been working for me and now I can't even use the Submit button I created on
the excel file. Starting from scratch doesn't seem to do anything either. Any
other suggestions?

Sigh!

 

[Ron de Bruin]

Start with a new file and test the code there

 

[MeMe]

Hi Ron,

Starting over gave me the signature and a command button that works thank
you very much. But the file no longer wants to be sent - it doesn't go with
the e-mail. The e-mail has no attachment even when I added it to the
Attachments.Add portion. I typed it in as " '.Attachments.Add
(P:\Percentages.xls").

I appreciate yoru assistance!

 

[MeMe]

is there a way to delete a message. Sorry Ron, I just removed the " ' " and
it works fine.

Sorry for being such a trial.

Take care!