Send WorkSHEET as Email Attachment via Excel - LOTUS Notes

[BuzzOffSweetheart]

I need help putting a macro button on an individual worksheet so the user can click a big easy button and a new email will pop up in Lotus notes with the worksheet attached for them to input the address and then send the worksheet..

Any one know if I can do this or how I go about to accomplish this?

I prefer to:
Send individual worksheets only
The ability to input the email address is the new composition email in LOTUS Notes

Thanks in advance!

 

[VoG]

Which version of Excel are you using?

 

[BuzzOffSweetheart]

2003 SP3 - Build (11.8211.8292)

 

[VoG]

Perhaps this:

  With ActiveSheet
    .Copy
    stFileName = .Range("A1").Value
  End With

It is expecting to find the filename to be saved as (without .xls) in A1 of the active sheet. Could that be it?

 

[BuzzOffSweetheart]

I finally figured it out, with your help of course.. After that reply.. I went in looked over the code..

'Copy the active sheet to a new temporarily workbook.
  With ActiveSheet
    .Copy
    stFileName = .Range("A1").Value
  End With
  stAttachment = stPath & "\" & stFileName & ".xls"

And I changed this..

'Copy the active sheet to a new temporarily workbook.
  With ActiveSheet
    .Copy
    stFileName = .Range("A1").Value
  End With
  stAttachment = stPath & "\" & stFileName & "Workhours.xls"

How silly of me, I assumed I didn't need to put in the filename..

But it worked!!

It also popped up my dialogue box to enter my recipients, with the code you suggested, and it worked beautifully.

Thank you so much, VoG!!

So for anyone else that needs this in the future..

*Lotus Notes 7.0.2
*MS Excel 2003

Code (with variables needing to be changed in red):

Const EMBED_ATTACHMENT As Long = 1454
Const stPath As String = "C:\Directory(MustExist)"
Const stSubject As String = "Email Subject Line"
Const vaMsg As Variant = "Email Body Message." & vbCrLf & _
                                          "Salutation," & vbCrLf & _
                                           "Name"
Const vaCopyTo As Variant = "youremail@email.com"
 
Sub CommandButton1_Click()
  Dim stFileName As String
  Dim vaRecipients As Variant
  Dim noSession As Object
  Dim noDatabase As Object
  Dim noDocument As Object
  Dim noEmbedObject As Object
  Dim noAttachment As Object
  Dim stAttachment As String
 
  'Copy the active sheet to a new temporarily workbook.
  With ActiveSheet
    .Copy
    stFileName = .Range("A1").Value
  End With
  stAttachment = stPath & "\" & stFileName & "WhatYouWantTheFileNameToBeThatYouEmail.xls"
  'Save and close the temporarily workbook.
  With ActiveWorkbook
    .SaveAs stAttachment
    .Close
  End With
  'Create the list of recipients.
  vaRecipients = Split(InputBox("Enter list of addresses separated with commas"), ",")
  'Instantiate the Lotus Notes COM's Objects.
  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
  'Create the e-mail and the attachment.
  Set noDocument = noDatabase.CreateDocument
  Set noAttachment = noDocument.CreateRichTextItem("stAttachment")
  Set noEmbedObject = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", stAttachment)
  'Add values to the created e-mail main properties.
  With noDocument
    .Form = "Memo"
    .SendTo = vaRecipients
    .CopyTo = vaCopyTo
    .Subject = stSubject
    .Body = vaMsg
    .SaveMessageOnSend = True
    .PostedDate = Now()
    .Send 0, vaRecipients
  End With
  'Delete the temporarily workbook.
  Kill stAttachment
  'Release objects from memory.
  Set noEmbedObject = Nothing
  Set noAttachment = Nothing
  Set noDocument = Nothing
  Set noDatabase = Nothing
  Set noSession = Nothing
  MsgBox "This is the confirmation message after the email has been sent sucessfully", vbInformation
End Sub

 

[VoG]

You are welcome

For what it's worth I find that you can rarely grab a piece of code off the web and get it to work 'straight out of the box'. There's usually a few tweaks necessary and if you don't read the code carefully it is easy to generate errors even with code that has obviously been tested extensively. Having said that, the web page that I linked you to could have been more explicit about the requirements - not everybody who comes across that page will be a programmer.

Gripe over

 

[mrholiday]

Hello all,

I have an small issue with the Lotus Notes Password.

After the code line:
Set noSession = CreateObject("Notes.NotesSession")

Lotus Notes opens and asks me for my Lotus Notes Access Password
I need to enter this manually in the Lotus Notes Password Window and only then the code continues running.

There is somehow a code line which should input the password automactically:

NoSession.Initialize ("password")

but I do not manage to get it working.


does somebody have an idea of how to write this code line correctly?


Best wishes from Luxemburg
Jerry

 

[mrpaw]

I've found this the cleanest way to send a single worksheet from an Excel workbook to one or more Lotus Notes recipients:


Sub EmailReport()

'cell you want to start with - you can elminate if needed
Range("A1").Select
Application.ScreenUpdating = False
ActiveSheet.Copy
Application.Dialogs(xlDialogSendMail).Show _
arg1:="emailaddress1@domain.com, emailaddress2@domain.com", _
arg2:="This goes in the SUBJECT line area"
ActiveWorkbook.Close SaveChanges:=False

Application.DisplayAlerts = False




End Sub