Solved

Send excel file from Lotus Notes

Posted on 2014-07-28
7
164 Views
Last Modified: 2014-07-29
From within the Excel file I am working I will create a new file and save it with a  name [i.e. ReconReport.xls]

I will then need to create a Lotus Notes e-mail to one or more recipients [but it may be better to have a pop up window that allows the user to add their own recipients rather than hard coding them if poosible]

I then need to attach the Excel file ReconReport.xls into Lotus Notes e-mail and for the e-mail to autosend to the recipients.

This sounds so easy but I know its not. Is there and Expert out there that could provide me with the VBA code to do this. I have searched EE but there is not an exact solution that I could work with.

Many thanks
0
Comment
Question by:Jagwarman
  • 4
  • 3
7 Comments
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40223654
Hi,

pls try

Sub Send_Sheets_Notes_Email()
 
  Const EMBED_ATTACHMENT As Long = 1454
 
  Dim varRecipients As Variant

 
  Dim noSession As Object
  Dim noDatabase As Object
  Dim noDocument As Object
  Dim noEmbedObject As Object
  Dim noAttachment As Object
 
  Dim wbBook As Workbook

  On Error GoTo Error_Handling
 
  Application.ScreenUpdating = False
 
  Set wbBook = ThisWorkbook

   wbBook.Save
   strAttachment = wbBook.Path & "\" & wbBook.Name

   Set noSession = CreateObject("Notes.NotesSession")
   Set noDatabase = noSession.GETDATABASE("", "")

   If noDatabase.IsOpen = False Then noDatabase.OPENMAIL

   Set noDocument = noDatabase.CreateDocument
   Set noAttachment = noDocument.CreateRichTextItem("myAttachment")
   Set noEmbedObject = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", strAttachment)


   strRecipients = Application.InputBox( _
           Prompt:="Please add the name of the recipients.", _
           Title:="Recipients", Type:=2)
   If strRecipients = False Then Exit Sub
   
   varRecipients = Split(strRecipients, ";")

   With noDocument
     .Form = "Memo"
     .SendTo = varRecipients
     .Subject = "mySubject"
     .Body = "myBody"
     .SaveMessageOnSend = True
     .PostedDate = Now()
     .Send 0, varRecipients
   End With
 
  MsgBox ("The e-mail has successfully been created and distributed."), vbInformation
 
ExitSub:
  Set noEmbedObject = Nothing
  Set noAttachment = Nothing
  Set noDocument = Nothing
  Set noDatabase = Nothing
  Set noSession = Nothing
 
  Exit Sub
 
Error_Handling:
  MsgBox "Error number: " & Err.Number & vbNewLine & _
      "Description: " & Err.Description, vbOKOnly
  Resume ExitSub
End Sub

Open in new window

Regards
0
 

Author Comment

by:Jagwarman
ID: 40223804
Rgonzo 1971, that is brilliant except [and this is probably down to the way I wrote out my request] it attached the file that Macro is in rather than the file that I create from the Macro.

So, I have a file with a macro, I then create a new file i.e.

Dim NewBook As Workbook

Set NewBook = Workbooks.Add
    With NewBook
        .Title = "ReconReport"
        .Subject = "ReconReport"
        .SaveAs Filename:="ReconReport.xls"
    End With


I then copy the 3 tabs from my original file to this new file and it is this new file that I want to send out.

Is it me am I doing something wrong?

Thanks in Advance.
0
 
LVL 50

Expert Comment

by:Rgonzo1971
ID: 40223847
pls try

    Set wbBook = ThisWorkbook


    wbBook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy

    Set NewBook = ActiveWorkbook
    
    With NewBook
        .Title = "ReconReport"
        .Subject = "ReconReport"
        .SaveAs Filename:="ReconReport.xls"
    End With
    strAttachment = NewBook.Path & "\" & NewBook.Name
    newBook.Close

Open in new window

Change sheet names
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Jagwarman
ID: 40224325
Hi Rgonzo1971,

Maybe it is me that is not understanding your replies to me but, your last reply so far as I can tell copies the data from my original file to the new file called ReconReport and then closes ReconReport file.

What I need is for the new file ReconReport to be sent out as the attachment.

Am I misunderstanding your code?

Again thanks in advance
0
 
LVL 50

Accepted Solution

by:
Rgonzo1971 earned 500 total points
ID: 40224419
let's try
Sub Send_Sheets_Notes_Email()
 
  Const EMBED_ATTACHMENT As Long = 1454
 
  Dim varRecipients As Variant

 
  Dim noSession As Object
  Dim noDatabase As Object
  Dim noDocument As Object
  Dim noEmbedObject As Object
  Dim noAttachment As Object
 
  Dim wbBook As Workbook

  On Error GoTo Error_Handling
 
  Application.ScreenUpdating = False
 
    Set wbBook = ThisWorkbook


    wbBook.Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Copy

    Set NewBook = ActiveWorkbook
    
    With NewBook
        .Title = "ReconReport"
        .Subject = "ReconReport"
        .SaveAs Filename:="ReconReport.xls"
    End With
    strAttachment = NewBook.Path & "\" & NewBook.Name
    newBook.Close


   Set noSession = CreateObject("Notes.NotesSession")
   Set noDatabase = noSession.GETDATABASE("", "")

   If noDatabase.IsOpen = False Then noDatabase.OPENMAIL

   Set noDocument = noDatabase.CreateDocument
   Set noAttachment = noDocument.CreateRichTextItem("myAttachment")
   Set noEmbedObject = noAttachment.EmbedObject(EMBED_ATTACHMENT, "", strAttachment)


   strRecipients = Application.InputBox( _
           Prompt:="Please add the name of the recipients.", _
           Title:="Recipients", Type:=2)
   If strRecipients = False Then Exit Sub
   
   varRecipients = Split(strRecipients, ";")

   With noDocument
     .Form = "Memo"
     .SendTo = varRecipients
     .Subject = "mySubject"
     .Body = "myBody"
     .SaveMessageOnSend = True
     .PostedDate = Now()
     .Send 0, varRecipients
   End With
 
  MsgBox ("The e-mail has successfully been created and distributed."), vbInformation
 
ExitSub:
  Set noEmbedObject = Nothing
  Set noAttachment = Nothing
  Set noDocument = Nothing
  Set noDatabase = Nothing
  Set noSession = Nothing
 
  Exit Sub
 
Error_Handling:
  MsgBox "Error number: " & Err.Number & vbNewLine & _
      "Description: " & Err.Description, vbOKOnly
  Resume ExitSub
End Sub

Open in new window

0
 

Author Comment

by:Jagwarman
ID: 40226090
Rgonzo1971 that is exactly what I need. It's never until I see the end result of what I ask for that something else springs to mind and in this case I really should have said I needed the data on the ReconReport Tabs protected. If that is not possible not to worry you have already more than earned the 500 points. Thanks for your help with this one.
0
 

Author Closing Comment

by:Jagwarman
ID: 40226098
brilliant
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Excel vba question 7 39
Need transfer the format too ... 3 36
Need to combine three formulas into one 5 34
Excel printing an invoice header over two sheets 3 25
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

740 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question