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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,927
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