E-mailing a Workbook as an attachment, but...

[JMH022]

I have a problem on a project in the works which probably has a simple solution, but for the life of me it is stumping me. Here is the code that works:

Sub SendMail ()

‘Assumptions: User has Microsoft Outlook open, User has Excel open and a file titled ‘Book 1’ is active in Excel.

'This sends the attachment via e-mail to multiple recipients.

Windows("Book 1.xls").Activate

ActiveWorkbook.SendMail Recipients:=Array("email1@domain.com", "email2@domain.com"), Subject:=Date & _
" Subject of e-mail goes here", ReturnReceipt:=True

End Sub


‘The item is then saved in the Sent Items folder. I want to add code to be able to send the item without a copy being saved to the Sent Items folder. Any experts out there that can tell me if this is possible?

 

[Al Chara]

I don't know if this will help you, but you can turn off the option in Outlook:
Tools|Options|Preferences|E-mail Options...|uncheck Save copies of messages in Sent Items folder

Hope this helps.
Kind regards, Al.

 

[JMH022]

Yes, Thank you for your reply. I have considered that as well, but I only want to shut it off for this disbursement and then turn it back on after the e-mails have been sent.

I am sending out workbooks close to 1 MB in size to multiple recipients. Even zipped, the capacity of my mailbox is exceeded if I try to send all of the workbooks I need to send. Not saving them in "Sent Items" will keep my mailbox administrator from sending "nasty-grams" to me. That's the hope anyway.

 

[Jack in the UK]

Hi Jim

I know this will not be welcomed, but ill be honest if blunt

Please dont!

This what you as is not so easy and all be honest is along the lines of VISUS, thats their trick, and i dont like it, i know some careful programmers that can get arround this but putting in send and deleteing it on arival, but i say no more. Its mucjk and i do not approve.

I know whay you want and agree i would also, but outlook wont like it, and this JUMPING can havenasty effect on Mapi32 the email structure on Emails, and will cause probs.

What i have to do is this i have a bulk TEXT ONLY mailer in VBA, i bastardised, which hits thousdands on the fly and add to sent..... Really cool, but its like a spammer so i wont let others have it as it can flood server with ease

VBA rate at 10mbs worth of files a second thats 100 to 1000 see its nasty stuffif abused.

I hit sections so sent X and delete and send Y ui opperate from UK under French rules but UK laws, ?? Odd yes, if i were see watching **** in UK by Frane this laws are different but in UK very different, so these email trick your find also in UK will not be liked. with attachments server have sweepers that track volume and same files, this will flow a fuse if this is attacked with many emails as i have found. Attachments make it worse.. Virus send same attachments many times FAST, can be Excel file like the french virus Laurus 1 and larus 2 were VBA Macro nasty virus.

A little histotry to nasty people mucking up our honest work and honest ententions - i feel the same as you...

take care, ....

 

[g_erhard]

I run into that problem here at work as well, as we all seem to have this insane addiction to ATTACHMENTS...

A couple of things that I've done to combat "Sent Items Bloat" is:

• Save the file being 'sent' to a network folder that all recipients can read, make a shortcut to it, and send the shortcut...
• CC or BCC everybody on one mailing (thus only one copy is in Sent Items)
• Set up a filter to automatically "sweep" large attachments from Sent into a PST file as they are sent (this keeps the Sent Items folder small, but can result in a HUGE PST file.. so best to create a new one or each mass mailing if you need archive copies).

Hopefully these suggestions help...

G Erhard
Course Developer/Instructor
SBC Internet Services
This message was edited by g_erhard on 2002-03-14 15:13

 

[dk]

Hi,

If you're using Outlook then you can use this code. It sends the activeworkbook WITHOUT saving a copy of the item in the Sent Items folder.

Sub SendAWorkbook()
'Sends active workbook from Outlook without saving in Sent Items

Dim olApp As Object
Dim olMail As Object

Set olApp = CreateObject("Outlook.Application")
Set olMail = olApp.CreateItem(0)
olMail.To = "someone@somewhere.com;someoneelse@somewhereelse.co.uk"
olMail.Attachments.Add ThisWorkbook.FullName
olMail.Subject = "Hello  from Excel"
olMail.Body = "Please find attached an Excel workbook for your viewing pleasure"
olMail.DeleteAfterSubmit = True
olMail.Send
End Sub

Hope this helps,

Let me know if you have any problems,

D

 

[Jack in the UK]

dk

i understand, but thats one offs, can it bulh say 100 or 1000 on the fly?

Or need amendments to X and ref x to data sheet for send to and loop ....

 

[Guest]

dk

i understand, but thats one offs, can it bulh say 100 or 1000 on the fly?

Or need amendments to X and ref x to data sheet for send to and loop ....

Jack,

The code can be changed to do practically anything you want. To do what you said you'd could just put the code in a loop e.g.

Option Explicit


Sub SendWorkbooks()
'This code assumes that you have 1000 email addresses in the range A1:A1000
'It will send an attachment to each of them without saving anything to the
'Sent Items folder.  BEWARE!  The code may take some time to run depending on the
'speed of you PC and your internet connection.


''''''READ THESE TWO LINES - THE CODE WILL FAIL IF YOU DON'T!!!!!!
'!!!!!This code needs references to the Outlook object library!!!!
'!!!!!You can do this from Tools, References in the VBE!!!!!!!!!!!



Dim olApp As Outlook.Application
Dim olMail As Outlook.MailItem
Dim strAttachmentPath As String
Dim lngLoop As Long

Set olApp = New Outlook.Application

'This could be anything e.g. different for each person, a different file depending
'on the date, a different file depending on their weight
strAttachmentPath = "C:tempsomeFile.xls"



For lngLoop = 1 To 1000
    Set olMail = olApp.CreateItem(0)
    olMail.To = Cells(lngLoop, 1)
    olMail.Attachments.Add strAttachmentPath
    'The email subject can be set here - you could take it from a cell for example
    olMail.Subject = "Hello  from Excel"
    'Likewise with the body of the email - can be changed here
    olMail.Body = "Please find attached an Excel workbook for your viewing pleasure"
    olMail.DeleteAfterSubmit = True
    olMail.Send
Next lngLoop

End Sub

Regards,
D

 

[JMH022]

Thank you all for your replies.

g_erhard, I appreciate the suggestion about the network folder. I will consider this for those recipients with connection to the network. These leaves a large number of people outside the company who receive this file. I will try out your code and see if it works for me.

Jack, I also understand the concern you presented about conflicting with Outlook's internal works. I am concerned that I could inadvertantly create havoc by trying to streamline my procedures. I am not sending this to thousands of people. Rather, the most would be less than 100 different recipients.

Is it possible that I could accidentally 'breed' a virus by doing this?

 

[JMH022]

dk:
I tried your code and it worked...kind of.

It did send an attachment file using Outlook and it did not save a copy in the Sent Items folder.

The problem is, it did not send the active workbook as the attachment. Rather, it sent my 'hidden' Personal Macro Workbook as the attachment.

I think the problem lies in the olMail.Attachments.Add ThisWorkbook.FullName statement. How to I reword this to send the active sheet. I tried Windows("Book1").Activate and then sending it, but it errored. Do I need to specify the actual name of the file somewhere?