Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending an email from Excel attaching the workbook they are in
Does anyone know how to send an Excel Workbook as an attachment in a email
from a button on the Excel workbook. We use Lotus notes as our email so if anyone has done something like this and can point me in the right direction I would greatly appreciate it. If there is anyway to save the email in the person's mailbox that would be the topping on the cake also. Thanks in advance, John |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending an email from Excel attaching the workbook they are in
Hi John I asked a similar question recently in regards to being able to send email when not using Outlook. Ron de Bruin kindly responded: CDO is a option maybe for you http://www.rondebruin.nl/cdo.htm I have not had time to check it out but it may cover what you are looking for. Thanks Andy John wrote: Does anyone know how to send an Excel Workbook as an attachment in a email from a button on the Excel workbook. We use Lotus notes as our email so if anyone has done something like this and can point me in the right direction I would greatly appreciate it. If there is anyway to save the email in the person's mailbox that would be the topping on the cake also. Thanks in advance, John |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending an email from Excel attaching the workbook they are in
I have some code to email from Lotus notes. I found it a while ago on a
few web sites - there's two versions that I cobbled together to test but the project fell through so neither version has ever been tested. I hope it helps you out - if any of it does work it would be useful to know! Watch for wrapping... Public Function fcnSendLotusNotesMailV1(Subject As String, Attachment As String, Recipient As Variant, BodyText As String, SaveIt As Boolean) As String Dim myErrs As String 'Set up the objects required for Automation into lotus notes Dim Maildb As Object 'The mail database Dim UserName As String 'The current users notes name Dim MailDbName As String 'THe current users notes mail database name Dim MailDoc As Object 'The mail document itself Dim AttachME As Object 'The attachment richtextfile object Dim Session As Object 'The notes session Dim EmbedObj As Object 'The embedded object (Attachment) On Error GoTo ErrorHandler 'Start a session to notes Set Session = CreateObject("Notes.NotesSession") 'Next line only works with 5.x and above. Replace password with your password 'Session.Initialize ("password") 'Get the sessions username and then calculate the mail file name 'You may or may not need this as for MailDBname with some systems you 'can pass an empty string or using above password you can use other mailboxes. UserName = Session.UserName MailDbName = Left$(UserName, 1) & Right$(UserName, (Len(UserName) - InStr(1, UserName, " "))) & ".nsf" 'Open the mail database in notes Set Maildb = Session.GETDATABASE("", MailDbName) If Not Maildb.IsOpen Then Maildb.OPENMAIL End If 'Set up the new mail document Set MailDoc = Maildb.CreateDocument MailDoc.Form = "Memo" MailDoc.SendTo = Recipient MailDoc.Subject = Subject MailDoc.Body = BodyText MailDoc.SaveMessageOnSend = SaveIt 'Set up the embedded object and attachment and attach it If Attachment < "" Then Set AttachME = MailDoc.CreateRichTextItem("Attachment") Set EmbedObj = AttachME.EmbedObject(1454, "", Attachment, "Attachment") '''''MailDoc.CREATERICHTEXTITEM ("Attachment") End If 'Send the document MailDoc.PostedDate = Now() 'Gets the mail to appear in the sent items folder MailDoc.Send 0, Recipient ErrorHandler: 'Clean Up Set Maildb = Nothing Set MailDoc = Nothing Set AttachME = Nothing Set Session = Nothing Set EmbedObj = Nothing Err.Clear End Function Function fcnSendWithLotusV2(myPath As String, stTitle As String, stBody As String, myRecipients As Variant) as boolean Dim myErrs As String Dim noSession As Object, noDatabase As Object, noDocument As Object Dim obAttachment As Object, EmbedObject As Object Dim stAttachment As String Dim vaRecipient As Variant, vaMsg As Variant Const EMBED_ATTACHMENT As Long = 1454 'Instantiate the Lotus Notes COM's Objects. On Error GoTo ErrorHandler Set noSession = CreateObject("Notes.NotesSession") Set noDatabase = noSession.GETDATABASE("", "") 'If Lotus Notes is not open then open the mail-part of it. If noDatabase.IsOpen = False Then noDatabase.OPENMAIL End If 'Create the e-mail and the attachment. Set noDocument = noDatabase.CreateDocument Set obAttachment = noDocument.CreateRichTextItem("stAttachment") Set EmbedObject = obAttachment.EmbedObject(EMBED_ATTACHMENT, "", myPath) 'Add values to the created e-mail main properties. With noDocument .Form = "Memo" .SendTo = myRecipients .Subject = stTitle .Body = stBody .SaveMessageOnSend = True End With 'Send the e-mail. With noDocument .PostedDate = Now() .Send 0, myRecipients End With fcnSendWithLotusV2 = true ErrorHandler: 'Clean Up Set EmbedObject = Nothing Set obAttachment = Nothing Set noDocument = Nothing Set noDatabase = Nothing Set noSession = Nothing End Function John wrote: Does anyone know how to send an Excel Workbook as an attachment in a email from a button on the Excel workbook. We use Lotus notes as our email so if anyone has done something like this and can point me in the right direction I would greatly appreciate it. If there is anyway to save the email in the person's mailbox that would be the topping on the cake also. Thanks in advance, John |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending an email from Excel attaching the workbook they are in
See this site
Sending mail from Lotus Notes (XL-Dennis) http://www.excelkb.com/?cNode=1X5M7A -- Regards Ron de Bruin http://www.rondebruin.nl "John" wrote in message ... Does anyone know how to send an Excel Workbook as an attachment in a email from a button on the Excel workbook. We use Lotus notes as our email so if anyone has done something like this and can point me in the right direction I would greatly appreciate it. If there is anyway to save the email in the person's mailbox that would be the topping on the cake also. Thanks in advance, John |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending an email from Excel attaching the workbook they are in
Thanks for you help Ron.
One question. For some reason when notes is closed and I run the code, it opens up Notes, is there a way to run it without opening notes? Thanks, John "Ron de Bruin" wrote: See this site Sending mail from Lotus Notes (XL-Dennis) http://www.excelkb.com/?cNode=1X5M7A -- Regards Ron de Bruin http://www.rondebruin.nl "John" wrote in message ... Does anyone know how to send an Excel Workbook as an attachment in a email from a button on the Excel workbook. We use Lotus notes as our email so if anyone has done something like this and can point me in the right direction I would greatly appreciate it. If there is anyway to save the email in the person's mailbox that would be the topping on the cake also. Thanks in advance, John |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending an email from Excel attaching the workbook they are in
Hi John
Never work with Notes myself. Send Dennis a mail, I am sure he will help you -- Regards Ron de Bruin http://www.rondebruin.nl "John" wrote in message ... Thanks for you help Ron. One question. For some reason when notes is closed and I run the code, it opens up Notes, is there a way to run it without opening notes? Thanks, John "Ron de Bruin" wrote: See this site Sending mail from Lotus Notes (XL-Dennis) http://www.excelkb.com/?cNode=1X5M7A -- Regards Ron de Bruin http://www.rondebruin.nl "John" wrote in message ... Does anyone know how to send an Excel Workbook as an attachment in a email from a button on the Excel workbook. We use Lotus notes as our email so if anyone has done something like this and can point me in the right direction I would greatly appreciate it. If there is anyway to save the email in the person's mailbox that would be the topping on the cake also. Thanks in advance, John |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending an email from Excel attaching the workbook they are in
Hi Ron,
I've been using your code without a hitch to create an email, attach a file and send it off. I am having a problem with one user though, and am getting a run-time error '-2147024770 (8007007e)' Automation Error The specified module could not be found. Have you run into this before? The code is stopping at this line: Set OutApp = CreateObject ("Outlook.Application") Thanks! David "Ron de Bruin" wrote: See this site Sending mail from Lotus Notes (XL-Dennis) http://www.excelkb.com/?cNode=1X5M7A -- Regards Ron de Bruin http://www.rondebruin.nl "John" wrote in message ... Does anyone know how to send an Excel Workbook as an attachment in a email from a button on the Excel workbook. We use Lotus notes as our email so if anyone has done something like this and can point me in the right direction I would greatly appreciate it. If there is anyway to save the email in the person's mailbox that would be the topping on the cake also. Thanks in advance, John |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending an email from Excel attaching the workbook they are in
Hi David
Is Outlook installed on this machine ? And have you set the reference in the VBA -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Hi Ron, I've been using your code without a hitch to create an email, attach a file and send it off. I am having a problem with one user though, and am getting a run-time error '-2147024770 (8007007e)' Automation Error The specified module could not be found. Have you run into this before? The code is stopping at this line: Set OutApp = CreateObject ("Outlook.Application") Thanks! David "Ron de Bruin" wrote: See this site Sending mail from Lotus Notes (XL-Dennis) http://www.excelkb.com/?cNode=1X5M7A -- Regards Ron de Bruin http://www.rondebruin.nl "John" wrote in message ... Does anyone know how to send an Excel Workbook as an attachment in a email from a button on the Excel workbook. We use Lotus notes as our email so if anyone has done something like this and can point me in the right direction I would greatly appreciate it. If there is anyway to save the email in the person's mailbox that would be the topping on the cake also. Thanks in advance, John |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending an email from Excel attaching the workbook they are in
Yes...Outlook is installed. Outlook 2003. I'm not sure what you mean by set
the reference in the VBA, but I am using your complete code. It works on all other locations except this one. Thanks for answering...I just can't figure this one out. David "Ron de Bruin" wrote: Hi David Is Outlook installed on this machine ? And have you set the reference in the VBA -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Hi Ron, I've been using your code without a hitch to create an email, attach a file and send it off. I am having a problem with one user though, and am getting a run-time error '-2147024770 (8007007e)' Automation Error The specified module could not be found. Have you run into this before? The code is stopping at this line: Set OutApp = CreateObject ("Outlook.Application") Thanks! David "Ron de Bruin" wrote: See this site Sending mail from Lotus Notes (XL-Dennis) http://www.excelkb.com/?cNode=1X5M7A -- Regards Ron de Bruin http://www.rondebruin.nl "John" wrote in message ... Does anyone know how to send an Excel Workbook as an attachment in a email from a button on the Excel workbook. We use Lotus notes as our email so if anyone has done something like this and can point me in the right direction I would greatly appreciate it. If there is anyway to save the email in the person's mailbox that would be the topping on the cake also. Thanks in advance, John |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending an email from Excel attaching the workbook they are in
Good morning
Try this first http://www.rondebruin.nl/mail/problems.htm -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Yes...Outlook is installed. Outlook 2003. I'm not sure what you mean by set the reference in the VBA, but I am using your complete code. It works on all other locations except this one. Thanks for answering...I just can't figure this one out. David "Ron de Bruin" wrote: Hi David Is Outlook installed on this machine ? And have you set the reference in the VBA -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Hi Ron, I've been using your code without a hitch to create an email, attach a file and send it off. I am having a problem with one user though, and am getting a run-time error '-2147024770 (8007007e)' Automation Error The specified module could not be found. Have you run into this before? The code is stopping at this line: Set OutApp = CreateObject ("Outlook.Application") Thanks! David "Ron de Bruin" wrote: See this site Sending mail from Lotus Notes (XL-Dennis) http://www.excelkb.com/?cNode=1X5M7A -- Regards Ron de Bruin http://www.rondebruin.nl "John" wrote in message ... Does anyone know how to send an Excel Workbook as an attachment in a email from a button on the Excel workbook. We use Lotus notes as our email so if anyone has done something like this and can point me in the right direction I would greatly appreciate it. If there is anyway to save the email in the person's mailbox that would be the topping on the cake also. Thanks in advance, John |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sending an email from Excel attaching the workbook they are in
Try adding "localhost" to CreateObject like this:
Set OutApp = CreateObject ("Outlook.Application", "localhost") "Ron de Bruin" wrote: Good morning Try this first http://www.rondebruin.nl/mail/problems.htm -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Yes...Outlook is installed. Outlook 2003. I'm not sure what you mean by set the reference in the VBA, but I am using your complete code. It works on all other locations except this one. Thanks for answering...I just can't figure this one out. David "Ron de Bruin" wrote: Hi David Is Outlook installed on this machine ? And have you set the reference in the VBA -- Regards Ron de Bruin http://www.rondebruin.nl "David" wrote in message ... Hi Ron, I've been using your code without a hitch to create an email, attach a file and send it off. I am having a problem with one user though, and am getting a run-time error '-2147024770 (8007007e)' Automation Error The specified module could not be found. Have you run into this before? The code is stopping at this line: Set OutApp = CreateObject ("Outlook.Application") Thanks! David "Ron de Bruin" wrote: See this site Sending mail from Lotus Notes (XL-Dennis) http://www.excelkb.com/?cNode=1X5M7A -- Regards Ron de Bruin http://www.rondebruin.nl "John" wrote in message ... Does anyone know how to send an Excel Workbook as an attachment in a email from a button on the Excel workbook. We use Lotus notes as our email so if anyone has done something like this and can point me in the right direction I would greatly appreciate it. If there is anyway to save the email in the person's mailbox that would be the topping on the cake also. Thanks in advance, John |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sending Workbook to an email address. | Excel Discussion (Misc queries) | |||
Sending 1 worksheet of a workbook as email file attachment | Excel Discussion (Misc queries) | |||
Attaching to an email corrupts workbook | Excel Discussion (Misc queries) | |||
Sending email WITHOUT attaching a workbook? | Excel Programming | |||
sending a workbook by email to multiple recipients | Excel Programming |