How to send email pressing a button from excel via lotus notes (a range)?

Job one:  I need to generate an email from an excel sheet that includes a range of cells that will change on a daily basis. The report needs to be sent out via Lotus Notes email. Right noe my code only sends out one cell.. and I cant make it to include a larger one.

The range I am trying to send is D7:G20  

Right now, alI I get in the body of the email is cell D7

code:

Sub SendLotusNotesMail()
Dim UserName As String
Dim MailDbName As String
Dim Maildb As Object
Dim MailDoc As Object
Dim AttachME As Object
Dim Session1 As Object
Dim EmbedObj1 As Object
Dim recep As Variant
Dim ccRecipient As Variant
ReDim recep(15)
ReDim ccRecipient(10)

'Open and locate current LOTUS NOTES User
'On Error GoTo testing
Set Session1 = CreateObject("Notes.NotesSession")
UserName = Session1.UserName
MailDbName = "Mail\yourmaildatabase.nsf"
Set Maildb = Session1.GETDATABASE("yourmailserver", MailDbName)
'Example: Set Maildb = Session1.GETDATABASE("maildb/server/company", MailDbName)
If Maildb.ISOPEN = True Then
Else
Maildb.OPENMAIL
End If

' Create New Mail and Address Title Handlers

attachment1 = Application.GetOpenFilename(, , "Please select file to send")

Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.form = "Memo"
recep(0) = ThisWorkbook.Worksheets("Main").Range("D5")
ccRecipient(0) = ThisWorkbook.Worksheets("Main").Range("D5")
subj = ThisWorkbook.Worksheets("Main").Range("D6")
mailbody = ThisWorkbook.Worksheets("Main").Range("D7:G20")
MailDoc.sendto = recep
MailDoc.CopyTo = ccRecipient
MailDoc.Subject = subj
MailDoc.Body = ThisWorkbook.Worksheets("Main").Range("D7:G20")
' Select Workbook to Attach to E-Mail


Call MailDoc.SEND(False)

Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session1 = Nothing
Set EmbedObj1 = Nothing

End Sub
 
 
olsoncaracasAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sjef BosmanGroupware ConsultantCommented:
The Body of a Notes mail should be (in this case) a rich-text field. It might be more forgiving than a mere textfield. But I suppose that won't work either.

Suggested approach:
- loop through all cells in your range
-    write the data, and a separator

I'm afraid there's little else...
0
nike_golfCommented:
It will need to be embeded as an object. Have you seen thi s Q?

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23485803.html

I believe the accepted solution #2 might be what your looking for.

NG,
0
olsoncaracasAuthor Commented:
nike golf,

I am trying the accepted solution #2. However I have never written a code before so there are a few questions I have:

1. What is/where do I find my server name.
i.e. Call workspace.OpenDatabase("ServerName", MailFile)

2. these are the changes I made to the origianl code tryig to customise it:

Steps -->
Create a new Excel Document
Open the Visual Basic for Applications tool bar
 --> View --> Tool bars -->  Check Control and Visual Basic
Place a VB Command Button on Worksheet1 along with the cells you want to Cut and Paste from excel to lotus notes

Paste the following code in the sub for CommandButton1_Click()
-------------------------------------------------
'the name of my worksheet is "one"
'the range i want o send in the body of my email is "c4:h19"
'my name is David Olson

Sheets("one").Select
Range("c4:h10").Select
Selection.Copy
Call NotesUIDoc.FIELDSETTEXT("EnterSendTo", "David Olson")
----------------------------------
it looks like this now and its not working (apparently because of the ServrName

Private Sub CommandButton1_Click()


    Dim CN, UserName, MailFile As String
   
    Dim workspace As Object
    Set workspace = CreateObject("Notes.NotesUIWorkspace")
   
    'Getting/creating the name of the current users mail DB file
    'Note this will only work on a Single user install of Lotus Notes that has a
    'common name convention e.g. FirstInitalfirst seven of last name

    Dim session As Object
    Set session = CreateObject("Notes.NotesSession")
    CN = session.COMMONUSERNAME
    UserName = LCase(Left(Left$(CN, 1) & Right$(CN, (Len(CN) - InStr(1, CN, " "))), 8)) & ".nsf"
    Set session = Nothing
    MailFile = "mail\" & UserName

    'Enter your Domino Server name in the following ServerName spot

    Call workspace.OpenDatabase("ServerName", MailFile)
   
    'Set your selection area on the worksheet (this is what will get pasted in your Notes Emial
   
    Sheets("one").Select
    Range("c4:h10").Select
    Selection.Copy
       
    Dim NotesUIDoc As Object
    Set NotesUIDoc = workspace.COMPOSEDOCUMENT("", "", "Memo")

    'Enter a valid user name in the Valid Username spot
    Call NotesUIDoc.FIELDSETTEXT("EnterSendTo", "David Olson")
    'Using a Domino Group above makes more sense for multiple users
    Call NotesUIDoc.FIELDSETTEXT("Subject", ">>>>>> This is a TEST Email Message <<<<<")
    NotesUIDoc.GOTOFIELD "Body"
    NotesUIDoc.Paste
    NotesUIDoc.SEND
    NotesUIDoc.Close

    'Clean it up
    Set NoteUIDoc = Nothing
    Set workspace = Nothing
 
    Range("A1").Select
    Application.CutCopyMode = False
           
End Sub



thanks for your help



0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

olsoncaracasAuthor Commented:
sjef Bosman,

Can you expand on:

Suggested approach:
- loop through all cells in your range
-write the data, and a separator

... i am new to this.

Thanks
0
Sjef BosmanGroupware ConsultantCommented:
Are you? You could have fooled me ;-) The pieces of code you presented made me assume that writing code is your daily job.

I'm not a VB programmer, but I could make an attempt...

This line

MailDoc.Body = ThisWorkbook.Worksheets("Main").Range("D7:G20")

should be changed into something like (in LotusScript, which is 98% VB)

Dim body As String
Dim range As String
Dim rnum As Integer, cnum As Integer
Dim sep As String

sep= ";" ' separator
For rnum=7 To 20
For cnum=4 To 7 ' D to G
rang= Mid$("ABCDEFGHIJ"; cnum; 1) + Cstr(rnum) ' should contain D7 the first time
body= body + Cstr(ThisWorkbook.Worksheets("Main").Range(rang))
body= body + sep
Next
body= body + chr$(13)
Next
MailDoc.Body= body
0
olsoncaracasAuthor Commented:
sjef Bosman,

I copy pasted the code from another post.. I didnt write it ...hehe.

anyways..this line come up in red.. and after I try clicking on the button I get error " cant execute the in break code"

rang= Mid$("ABCDEFGH"; cnum; 1) + Cstr(rnum) ' should contain D7 the first time.
0
Sjef BosmanGroupware ConsultantCommented:
Heh, that's where your experience should come in. I suppose the semicolons are illegal in VB. The correcter code might be

rang= Mid$("ABCDEFGH", cnum, 1) + Cstr(rnum) ' should contain D7 the first time.
0
nike_golfCommented:
Well it should be easy to paste in a range but it's not as I'm finding out... take a look at this solution, HTML is probably the way to go.

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_23316002.html?sfQueryTermInfo=1+rang+send

NG,
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
olsoncaracasAuthor Commented:
nike Golf,

That is what I want to do, but it says there it only works with Outlook. I use Lotus Notes.

I am now increasing the point to 500 for the question because I realise I know so little, that whoever makes this work.. deserves all the points.

thanks
0
Sjef BosmanGroupware ConsultantCommented:
Any progress on the modified code?
0
olsoncaracasAuthor Commented:
nor really, this is what I've got so far. Like I said previously.. I am completly ognorant in the matter so if there is vba/prgmng/excel lingo i cant use the info.

So far I can generate the email but only get one cell (out of the range I want to paste) in the body. Also, after I press the buttom, I get promptedt to attach something . I dont want to attach anything. Maybe you an give the code a try.. beacuse if you knwo programming... it should be pretty simple:

Sub SendLotusNotesMail()
Dim UserName As String
Dim MailDbName As String
Dim Maildb As Object
Dim MailDoc As Object
Dim Session1 As Object
Dim EmbedObj1 As Object
Dim recep As Variant
Dim ccRecipient As Variant
ReDim recep(15)
ReDim ccRecipient(10)

'Open and locate current LOTUS NOTES User
'On Error GoTo testing
Set Session1 = CreateObject("Notes.NotesSession")
UserName = Session1.UserName
MailDbName = "Mail\yourmaildatabase.nsf"
Set Maildb = Session1.GETDATABASE("yourmailserver", MailDbName)
'Example: Set Maildb = Session1.GETDATABASE("maildb/server/company", MailDbName)
If Maildb.IsOpen = True Then
Else
Maildb.OPENMAIL
End If

' Create New Mail and Address Title Handlers

attachment1 = Application.GetOpenFilename(, , "Please select file to send")

Set MailDoc = Maildb.CREATEDOCUMENT
MailDoc.form = "Memo"
recep(0) = ThisWorkbook.Worksheets("Main").Range("D5")
ccRecipient(0) = ThisWorkbook.Worksheets("Main").Range("D5")
subj = ThisWorkbook.Worksheets("Main").Range("D6")
mailbody = ThisWorkbook.Worksheets("Main").Range("D7:G20")
MailDoc.sendto = recep
MailDoc.CopyTo = ccRecipient
MailDoc.Subject = subj
MailDoc.Body = mailbody
' Select Workbook to Attach to E-Mail
MailDoc.SaveMessageOnSend = True



Call MailDoc.Send(False)

Set Maildb = Nothing
Set MailDoc = Nothing
Set AttachME = Nothing
Set Session1 = Nothing
Set EmbedObj1 = Nothing

End Sub
0
olsoncaracasAuthor Commented:
tks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Lotus IBM

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.