help with mail as attachment feature.

dragon007

New Member
Joined
Aug 6, 2002
Messages
43
hi everybody,

at work usually i need to send an excel file "quotation" to my clients by email as an attachment. so what i do is, i have this mai.xls workbook with one sheet, i copy all the data i want to send and "paste special" the values, because i don't want to send my actually file as it contains formulas and confidential data.

so what i am asking, is there a way that i can automate the process. like assigning a macro to a button, and when clicked it would:
open the file mail.xls
copy the data selected and "paste special" the values only without the formulas
put the file as an attachment
fill the subject field
fill the receipient field
and put a standard message to the email.

itried to record a macro, but i wasn't very successful.

i am not asking for everything to be done for me, just if someone can guide me throw or suggest some solutions, it would be a great help.

thanks
This message was edited by dragon007 on 2002-09-08 05:41
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
L

Legacy 98055

Guest
Hi
Define your source and destination ranges...
Ex. Copying Range("A1:D32") from activesheet to Sheets("SendMail").Range("B1:E32")
Will need sheet name of destination for a working example.
Tom
 

dragon007

New Member
Joined
Aug 6, 2002
Messages
43
thanks tom for your reply,
but actually i didn't understand anything of what you wrote.
can you please elaborate your reply a little bit more.
thanks
 
L

Legacy 98055

Guest

ADVERTISEMENT

Hi
Define your source and destination ranges...

The data you will be copying.

The range you are sending the data to on Mail.xls

Tom
 

dragon007

New Member
Joined
Aug 6, 2002
Messages
43
hi Tom,
in my case it's not possible to define the range that will be copied neither where it will be pasted. because the size of the "quotation" is always different, depending on the initial enquiry. it can be 50 rows, or 150 rows. i have no control over that.
i want to select all the data that i want to copy, and then hit the button to perform the tasks i mentioned in my first post.

any further suggestions will be very much appreciated.
thanks.
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123

ADVERTISEMENT

An easy way around this is to code a macro so that it copies the sheet into a new workbook using pastespeacial xlvalues.
Then send the new file.

Say Book1.xls is the normal file and book2 is a blank file and your currently in Book1

Range("a1:x10000").Copy (to cater for various amounts of data)
workboooks.open("..../Book2.xls")
Sheet1.activate
range("a1").pastespecial xlvalues
ActiveWorkbook.sendmail etc...

ActiveWorkboook.close savechanges = true
(closes Book2)

ActiveWorkbook.close savechanges = true
(closes Book1).
 

dragon007

New Member
Joined
Aug 6, 2002
Messages
43
thanks lasw10 for your reply,

i actually found the solution based on your code plus other code i found on other posts. this is the code:

Private Sub Button_Click()
Selection.Copy
Workbooks.Open Filename:= _
".../template.xls"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.Dialogs(xlDialogSendMail).Show _
arg1:="email@address.com", _
arg2:="Invoice name"
End Sub

i hope it will be useful for someone else to use.

cheers
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Not sure if this would be of any use to anyone - but as I am sure a lot of people are aware, Excel is extremely limited in it's flexibility to email using bog standard arguments. I believe there are only 2 basic ones (as attachment and routing slip). I came across a MAPIMAil function that enables you to create a dynamic message of any length - whilst also allowing for loads of variables (ie. recipient, attachments etc...). I use it loads on loop macros to distribute files automatically with recipient specific messages. If anyone wants the code let me know though I am sure a lot of people will have seent it elsewhere.

Luke
 

Forum statistics

Threads
1,144,127
Messages
5,722,634
Members
422,450
Latest member
Springbok

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
Top