?
Solved

Mailmerge in Lotus from csv/AccessDB/Excel

Posted on 2005-04-04
29
Medium Priority
?
638 Views
Last Modified: 2013-12-18
Dear all

due to complete lack of programming skills in Lotus Notes... :-(

is it possible with a already developed tool or a small script to create Emails in LN R6.5 which are then stored in the drafts folder.

The input data looks like this:
+++++++++++++++++++
Record 1: UserA;Mister;500$
Record 2: UserB;Miss;200$
Record 3: UserC;Mister;800$
...
Record n:.....

It should then create n emails stored in the drafts folder in the folloing format:

-----------------------------
To: UserA
Subject: Costs: 500$
Body:

Dear UserA

it costs 500$

Thanks a lot
-----------------------------


Can anybody help? Your endeavours are greatly appreciated
0
Comment
Question by:OliverAnthony_Lohri
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
  • 8
  • +1
29 Comments
 
LVL 5

Expert Comment

by:steve_bagnall
ID: 13697773
Hi OliverAnthony_Lohri,

Can I ask a question: What delimits your new lines; a CR (Carriage Return), NL (New Line) character, or both (if so in what order)?

Cheers,
Steve
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13697952
You can almost import the documents straight from a Lotus 123 file, with extension .wk4.

The recipe:
- make column headers that represent the fieldnames, in your case:
    Form, Subject, Body, SendTo
- the Form column should be filled with the word Memo
- Body may contain just text, and CR/LF may be used
- the other columns you can prepare already in the spreadsheet

Make the spreadsheet in Excel, then save it as a Lotus 123 (.wk4) file. In Lotus, open your mail database, then File, Import, select Lotus 123 type and your file, and there's not a lot that can go wrong.
0
 
LVL 5

Expert Comment

by:steve_bagnall
ID: 13698636
OliverAnthony_Lohri,

The following script will do the job for you.  You may need to tweak it to get it to work on your system, but just let me know what problems you are having and I will help you fix them:


Sub Initialize
   
    Dim LINE_DELIMETER As String
    Const FILENAME = "c:\temp\emails.txt"
   
    LINE_DELIMETER = Chr(13)+Chr(10)
   
    Dim fileNum As Integer
    Dim strLine As String
   
    fileNum = Freefile()
    Open FILENAME For Input As fileNum
   
    While strLine <> "-1"    
        If strLine <> "" Then
           
            MakeMail(strLine)    
           
        End If
        strLine = GetNextLine(fileNum, LINE_DELIMETER)
    Wend
   
   
End Sub


Function GetNextLine(fileNum As Integer, lineDelimeter As String)  
   
    Dim strInput As String
    Dim char As String
    Dim testStr As String
   
    strInput = "-1"
   
    On Error Goto EndOf
    char = Input(1, fileNum)
   
    strInput = ""
   
    While testStr <> lineDelimeter
       
        strInput = strInput + char
        char = Input(1, fileNum)
        testStr = Right(testStr,1) + char
       
    Wend
   
ReturnString:
    GetNextLine = strInput
    Exit Function
   
EndOf:
    Resume ReturnString
   
End Function



Sub MakeMail(strLine As String)
   
    Dim strTo As String
    Dim strSubj As String
    Dim strBody As String
   
    Dim firstColon As Integer
    Dim firstSemi As Integer
    Dim secSemi As Integer
    Dim length As Integer
   
    Dim s As notessession
    Dim db As notesdatabase
    Dim doc As notesdocument
   
    firstColon = Instr(1, strLine, ":")
    firstSemi = Instr(1, strLine, ";")
    secSemi = Instr(firstSemi + 1, strLine, ";")
    length = Len(strLine)
   
    strTo = Mid(strLine, firstColon + 2, length - firstcolon - (length - firstSemi) - 2)
    strSubj = "Costs: " + Mid(strLine, secSemi + 1, length - secSemi )
    strBody = "Dear " + strTo + Chr(13) + Chr(13) + "It costs " + Mid(strLine, secSemi + 1, length - secSemi ) + _
    Chr(13) + Chr(13) + "Thanks a lot."
   
    Set s = New notessession
    Set db = s.currentdatabase
   
    Set doc = New notesdocument(db)
   
    Call doc.replaceitemvalue("Form", "Memo")
    Call doc.replaceitemvalue("SendTo", strTo)
    Call doc.replaceitemvalue("Subject", strSubj)
    Call doc.replaceitemvalue("Body", strBody)
   
    Call doc.replaceitemvalue("PostedDate", "")
    Call doc.replaceitemvalue("$MessageType", "")
   
    doc.save 1, 1
   
    ' Drafts is a view in my version of Notes
'    Call doc.putinfolder("Drafts", True)    
   
End Sub
0
Industry Leaders: 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!

 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13698763
I love script, but File/Import... is A LOT easier for a lazy bum like me :)
0
 
LVL 5

Expert Comment

by:steve_bagnall
ID: 13698841
Not easier when you don't have to write it ;)
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13699338
Write what? There's no code involved? You mean with your code? It till has to be adapted or corrected, I suppose. And you need the Designer. New Agent. Copy. Paste. Save. Try. Change agent properties. Try again. And again... Ooooh, that's too much effort... *yawn*

Sjef ;)
0
 
LVL 5

Expert Comment

by:steve_bagnall
ID: 13704704
Maybe, but once it works, it works ... no manual intervention required, you could even get it to run on schedule.  Horses for courses I suppose.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13704779
OliverAnthony_Lohri,

May we have your opinion, please? At least some interaction with the asker would be very much appreciated...

Sjef
0
 

Author Comment

by:OliverAnthony_Lohri
ID: 13708089
Sorry...lots of meetings and I did not expect so fast and good answers...

But this import thing is way not so easy as you say...you need a form document, and what IS a form document? Second, I think you were in such a hurry to not carefully read the actual problem. I should be able to merge changing fields per record into a prewritten body. So far no 500 points for the lazy ones...

This script is complicated...is this an agent? how can i do something...really have no idea of programming...assistance is really appreciated

cheers for now
0
 

Author Comment

by:OliverAnthony_Lohri
ID: 13708136
ah and by the way...I do not have Lotus 123
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13708160
Form document?? Nope, is not required. Where did I mention that?

> ...hurry...
Huh?? I read it VERY carefully, and I thought it better to do the merging in Excel because that's a lot easier. So prepare everything in Excel, and then do the Import in one go.

So nothing complicated, all Excel!
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13708178
Lotus 1-2-3? Neither do I :) Excel can save to Lotus 1-2-3 format, that's just what you need.
0
 
LVL 5

Expert Comment

by:steve_bagnall
ID: 13708311
Hi,

Yes this is an agent.  Open Domino Designer and create a new agent in your mail file template (if you do not know how to do this then pass this on to your Notes Administrator to read and advise you of your options).  Name it CreateMails, click "Shared Agent".   Where it says "Which document(s) should it act on" select "Run Once (@commands may be used)" at the bottom of the list.  at the bottom where it says "run" change from "simple action(s)" to "LotusScript".  Click Initialize in the list that appears on the left.  

Copy my script as it appears above from "Sub Initialize" all the way to "End Sub".  Now back in Notes use your mouse and select all of the text in the right hand pane; it should be as follows:

Sub Initialize
      
End Sub

Now click paste, and do File-Save.  This should save OK, if not post here with any errors.

The second line in the script should read

Const FILENAME = "c:\temp\emails.txt"

Change the file path and name to the location and name of the text file that holds the input data and save again.

Refresh the design of your mail file using this template (agian if you do not understand this you will need to seek advice).

Go to your mail file in the Notes client and select Actions-CreateMails.  Wait for the agent to finish running.  Check your drafts view for the created mails.  If they are not there or you experience any other errors, post here with your findings.  This may take some time to get to work on your setup.

Cheers,
Steve
0
 
LVL 31

Assisted Solution

by:qwaletee
qwaletee earned 400 total points
ID: 13709033
Here's how to do it, fairly flexibly, no agent required or modifications to your mail file...

Create a file named costs_import.col -- it should be a text file with the content at the end of this posting
Go to your drafts view
File -> Import
Choose your input data file (not the .col file described by me)
Choose TABULAR TEXT
Click IMPORT
Tabular text import dialog appears:
    From the list of forms, choose Memo
    Enable "Use format file"
    Locate and select the .COL file (created per my instructions above)
    Click OK

That's it.  The next time, you will only have to select your input file as tabular, and select the Memo form again. Everything else will be preset for you.

Flexibility?  Read on, below the .COL fiel description.





Contents of costs_import.col:

(RecordNumber): UNTIL ":";
SendTo: UNTIL ";";
Honorific: UNTIL ";";
Amount: UNTIL "";
FORMULASTART

FIELD Subject := "Costs: " + @Text(Amount);
FIELD Body :=
    "Dear " + Honorific + " " + SendTo + ","
    + @NewLine
    + @NewLine + "It costs " + @Text(Amount)
    + @NewLine
    + @NewLine + "Thanks a lot"
+"";
FIELD Amount := @DeleteField;
FIELD Honorific := @DeleteField;
FIELD Form := "Memo";
FORMULAEND




How to make it flexible:



Need to change the content?  Look at the Subject := line... pretty easy to modify.  Similar for the message body -- I broke that up into several lines to make it easier to read and format.  @NewLine gets you to the next line, and @Text is used in case Notes thinks Amount is a numeric field, and iot can't put text and numerics together without @Text.

Need to import a file with a different format?  Look at the top part.  You put a fieldname: UNTIL "some marker"; and for the last field, the marker is "" instead of ";"

I also use some temporary fields -- Amount and Honorific.  The Notes mail file doens't want these, so I create them, for use in the Subject/Body formulas, then delete them with @DeleteField furthe ron in the .COL file.  I also get rid of the Record #: prefix by putting in a dummy field name in parentheses, (RecordNumber) -- which causes Notes to import the value (UNTIL ":") then throw it away.

0
 

Author Comment

by:OliverAnthony_Lohri
ID: 13714378
Hmm...sounds good.

I will first try the coding solution and then yours...then I'll decide...both seem to be very good, only that I am not an administrator for Lotus notes.

Small Question...if the body in the original mail would contain a table (say, I made an email beforehand with tables and formatting.) can i just copy the whole content of DocumentProperties -> Body Field
into the col file or the code?

Because that would be the best!!!!
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13714575
> ...if the body in the original mail would contain a table ...
What original mail? You intend to create a new mail, don't you? So there is no original mail, and no body. Which means that everything has to be defined when created.
0
 
LVL 5

Expert Comment

by:steve_bagnall
ID: 13714621
If you know the number of cells and the layout of the table before hand, you can do it using a template which the script will copy.  Let me know the format of the table and I will add the solution to the script I gave you.  Of course this will slightly complicate things as you will now have a template document that you will need to maintain.

Cheers,
Steve.
0
 

Author Comment

by:OliverAnthony_Lohri
ID: 13715669
I know...i just did not want to complicate things beforehand. Of course this has to be defined when created


It looks like this

-----------------------------
To: UserA
Subject: Costs: 500$
Body:

Dear UserA

it costs 500$

+++++++
+ Table +
+++++++

Thanks a lot
-----------------------------

The table is much more complicated to explain here, but remains static (no variables in it). The only thing which i would like to do is to have the fields Costs and Names as bold.

 Steve...I'd rather maintain a template document than spending each month 4 days doing 900 copy&paste.... thats why i give 500 points for this question :-)

cheers
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13715767
The question is answered, you have 2 ways to do it without programming (they are 75% alike) and one programmed.

You're making one too many last-minute request for me. Anyway, the question evolves way too much into a Rent-a-coder job...
0
 

Author Comment

by:OliverAnthony_Lohri
ID: 13715894
sjef...there are people who are willing to help each other out...If i can do something another can't I am very happy to help, regardless of "rent-a-skill".

if you think so, fine with me. You were never in the position to give any good solution. I'm sorry to say that. Your posts look like:

yeah, do that and do that ... it's easy...just close this question.

If you have the skills - what I dont want to decline that you haven't...you should leverage your teaching skills.

I am very happy that steve is helping me out and the other solution is certainly an assisted answer. It's in their responsibility to decide if they want to help me more or to say: Well this is the answer, you can do the rest with the assistance of the internet.

It is their decision not yours and I do not tolerate any negative energy here.

I'm fine with what I have, steve please tell me if you want to help me for the last bit or if you think that's enough. I'll respect it in either way and will then close the question
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 13716101
I disagree. My solution is a very good solution, it has worked for me in many, many cases. Qwaletees solution is a similar but better solution, since programming is shifted from Excel to Notes Formula language. Steve's approach leads to a different solution, yet more complex but infinitely more adaptable.

That my teaching skills are rather unpolished, I know, and I'm slowly improving. Those who listen well usually have no problems.
0
 
LVL 5

Expert Comment

by:steve_bagnall
ID: 13716163
I'm happy to give you the solution to the table thing no problem.  It's worth pointing out that importing a spreadsheet is a valid solution as sjef says; simpler, but more time intensive.

Can I ask as an aside how much Notes development knowledge you have, to save me from teaching you to suck eggs in my explaination?

Cheers,
Setve
0
 

Author Comment

by:OliverAnthony_Lohri
ID: 13716193
maybe it has worked for you, but the world is rather more...I don't want to get any more personal. I feel bad already to have stated my opinion. sjef your answer is not working for me. I'd like to concentrate on the two others. I dont want to do 2 times a merge.

I have an input file and the output documents. Nothing in between.

Thanks
0
 
LVL 5

Accepted Solution

by:
steve_bagnall earned 1600 total points
ID: 13716431
OK here is the procedure followed by the new script (replace the old script with this new one).  Let me know if there are any points which need further explaination (there probably will be if you're new to Notes!)

Cheers,
Steve


In the mail template as before:

Create a view called "CreateMailTemplate"
Edit the view, the column names dont really matter, but change the select criteria to:

SELECT Form = "CreateMailTemplate"

To change the select criteria, click anywhere on the white space in the middle of the screen whilst editing the view, and in the bottom left panel click on "View Selection".  In the bottom right pane where it says "Run" make sure "Formula" is selected.  In the white boc below this write the SELECT statement above.  Save the view.

I guess you know how to create a form as you have a table in one already:

Create a form called "CreateMailTemplate"
In the form write the word "Dear" followed by a space
Create a new field after the space (Create-Field).  Give it the name "USER"
Press Enter twice
Type "It costs" then a space and create another field, name it "COST"
Press Enter twice
Insert your table and format it as required
Below your table type "Thanks a lot"

Highlight your two fields and press Ctrl+B to make them bold.

Go to the menu item Design-Preview In Notes, save changes when asked.
In the preview select the menu item File-Save.
Press escape

Go to the view "CreateMailTemplate" in the Notes client

You should see one document there (the one you just saved) open it to make sure.  This is your template, if you ever want to change it - delete the document that is there now and go into your designer change it there and create a new document using the method above.  Make sure that the document always has the two fields USER & COST.

Note:  this template will also appear in your drafts folder - be careful not to delete it from there



START OF SCRIPT


Sub Initialize
      
      Dim LINE_DELIMETER As String
      Const FILENAME = "c:\temp\emails.txt"
      
      LINE_DELIMETER = Chr(13)+Chr(10)
      
      Dim fileNum As Integer
      Dim strLine As String
      
      
      fileNum = Freefile()
      Open FILENAME For Input As fileNum
      
      
      While strLine <> "-1"      
            If strLine <> "" Then
                  
                  MakeMail(strLine)      
                  
            End If
            strLine = GetNextLine(fileNum, LINE_DELIMETER)
      Wend
      
      
End Sub


Function GetNextLine(fileNum As Integer, lineDelimeter As String)  
      
      Dim strInput As String
      Dim char As String
      Dim testStr As String
      
      strInput = "-1"
      
      On Error Goto EndOf
      char = Input(1, fileNum)
      
      strInput = ""
      
      While testStr <> lineDelimeter
            
            strInput = strInput + char
            char = Input(1, fileNum)
            testStr = Right(testStr,1) + char
            
      Wend
      
ReturnString:
      GetNextLine = strInput
      Exit Function
      
EndOf:
      Resume ReturnString
      
      
      
End Function

Sub MakeMail(strLine As String)
      
      Dim strTo As String
      Dim strSubj As String
      Dim strBody As String
      
      Dim firstColon As Integer
      Dim firstSemi As Integer
      Dim secSemi As Integer
      Dim length As Integer
      
      Dim s As notessession
      Dim db As notesdatabase
      Dim doc As notesdocument
      
      ' ========================
      ' TEMPLATE MOD
      ' ========================      
      Const VIEW_NAME = "CreateMailTemplate"
      
      Dim tempView As notesview
      Dim tempDoc As notesdocument
      Dim rtiBody As notesrichtextitem
      
      ' ========================      
      
      
      
      firstColon = Instr(1, strLine, ":")
      firstSemi = Instr(1, strLine, ";")
      secSemi = Instr(firstSemi + 1, strLine, ";")
      length = Len(strLine)
      
      strTo = Mid(strLine, firstColon + 2, length - firstcolon - (length - firstSemi) - 2)
      strSubj = "Costs: " + Mid(strLine, secSemi + 1, length - secSemi )
      
      ' ========================
      ' TEMPLATE MOD
      ' ========================      
'      strBody = "Dear " + strTo + Chr(13) + Chr(13) + "It costs " + Mid(strLine, secSemi + 1, length - secSemi ) + _
'      Chr(13) + Chr(13) + "Thanks a lot."
      
      ' ========================            
      
      Set s = New notessession
      Set db = s.currentdatabase
      
      
      ' ========================
      ' TEMPLATE MOD
      ' ========================      
      
      Set tempView = db.getView(VIEW_NAME)
      Set tempDoc = tempView.getfirstdocument      
      
      Call tempDoc.replaceItemValue("USER", strTo)
      Call tempDoc.replaceItemValue("COST", Mid(strLine, secSemi + 1, length - secSemi ))
      tempDoc.save 1,1
      
      ' ========================
      
      Set doc = New notesdocument(db)
      
      Call doc.replaceitemvalue("Form", "Memo")
      Call doc.replaceitemvalue("SendTo", strTo)
      Call doc.replaceitemvalue("Subject", strSubj)
      
      ' ========================
      ' TEMPLATE MOD
      ' ========================      
'      Call doc.replaceitemvalue("Body", strBody)
      
      Set rtiBody = doc.CreateRichTextItem( "Body" )
      Call tempDoc.rendertoRTItem(rtiBody)
      ' ========================            
      Call doc.replaceitemvalue("PostedDate", "")
      Call doc.replaceitemvalue("$MessageType", "")
      
      
      doc.save 1, 1
      
      ' Drafts is a view in my version of Notes
'      Call doc.putinfolder("Drafts", True)      
      
      
      
End Sub



END OF SCRIPT
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 13719509
The type of simple import I described won't work with rich text content (tables, graphics, etc.).  For that, you either

1) Need a very scripted solution, like Steve's, or
2) You would have to create a custom form, with the "store form in document" option (which keeps your formatting when you send the message)

The way Steve handled it, you also need a custom form.  I woudl suggest you work with him to change that to use Stationery.  That way, you do nothave to create a form.  The same overall approach he gives would still work.
0
 
LVL 5

Expert Comment

by:steve_bagnall
ID: 13724579
Using stationary is a good idea as it keeps the custom document seperately in the folder (in my mail file) or in it's own folder (according to Notes help) and also you don't need to go into the designer to modify it.  However, I can't think of an easy way to get the users name and the cost into the body of a piece of stationary, especially not in a way that will still work if you want to change the template at any time in the future.

qwaletee, help me out here if you can.
0
 

Author Comment

by:OliverAnthony_Lohri
ID: 13734896
I am actually trying to implement the code.... I will post my progress here. Steve thanks a lot!!!!
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 13746103
steve_bagnall,

> qwaletee, help me out here if you can.
Depends.  If the variable text is all before or after the fixed text, it is doable.  Or, if teh customziation only uses the "header and footer" part of stationery, and you are free to discard the stationery body and replace it with your own.

- qwaletee
0
 

Author Comment

by:OliverAnthony_Lohri
ID: 13831481
Hi all

unfortunately I did not have the time to implement it completely but I will close the question to not let you wait longer. Thanks a lot for your help.

- Oli
0

Featured Post

Independent Software Vendors: 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

IBM Notes offer Encryption feature using which the user can secure its NSF emails or entire database easily. In this section we will discuss about the process to Encrypt Incoming and Outgoing Mails in depth.
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
Suggested Courses
Course of the Month14 days, 20 hours left to enroll

771 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