help with mail as attachment feature.

[dragon007]

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

 

[Legacy 98055]

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]

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

 

[dragon007]

any more suggestions?

 

[Legacy 98055]

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]

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]

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]

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]

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