Solved

Send excel file from Lotus Notes

Posted on 2014-07-28
7
157 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 48

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 48

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
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 

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 48

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now