Sending an email from Excel attaching the workbook they are in

[John]

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

[Andy]

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
>

[Gman]

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
>

[Ron de Bruin]

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" <[email protected]> wrote in message news:[email protected]...
> 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
>

[John]

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" <[email protected]> wrote in message news:[email protected]...
> > 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
> >
>
>
>

[Ron de Bruin]

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> > 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
>> >
>>
>>
>>

[David]

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" <[email protected]> wrote in message news:[email protected]...
> > 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
> >
>
>
>

[Ron de Bruin]

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" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> > 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
>> >
>>
>>
>>

[David]

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" <[email protected]> wrote in message news:[email protected]...
> > 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" <[email protected]> wrote in message news:[email protected]...
> >> > 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
> >> >
> >>
> >>
> >>
>
>
>

[Ron de Bruin]

Good morning

Try this first
http://www.rondebruin.nl/mail/problems.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl


"David" <[email protected]> wrote in message news:[email protected]...
> 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" <[email protected]> wrote in message news:[email protected]...
>> > 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" <[email protected]> wrote in message news:[email protected]...
>> >> > 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
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>