Mailmerge in Lotus from csv/AccessDB/Excel

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
OliverAnthony_LohriAsked:
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.

steve_bagnallCommented:
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
Sjef BosmanGroupware ConsultantCommented:
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
steve_bagnallCommented:
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Sjef BosmanGroupware ConsultantCommented:
I love script, but File/Import... is A LOT easier for a lazy bum like me :)
0
steve_bagnallCommented:
Not easier when you don't have to write it ;)
0
Sjef BosmanGroupware ConsultantCommented:
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
steve_bagnallCommented:
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
Sjef BosmanGroupware ConsultantCommented:
OliverAnthony_Lohri,

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

Sjef
0
OliverAnthony_LohriAuthor Commented:
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
OliverAnthony_LohriAuthor Commented:
ah and by the way...I do not have Lotus 123
0
Sjef BosmanGroupware ConsultantCommented:
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
Sjef BosmanGroupware ConsultantCommented:
Lotus 1-2-3? Neither do I :) Excel can save to Lotus 1-2-3 format, that's just what you need.
0
steve_bagnallCommented:
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
qwaleteeCommented:
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
OliverAnthony_LohriAuthor Commented:
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
Sjef BosmanGroupware ConsultantCommented:
> ...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
steve_bagnallCommented:
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
OliverAnthony_LohriAuthor Commented:
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
Sjef BosmanGroupware ConsultantCommented:
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
OliverAnthony_LohriAuthor Commented:
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
Sjef BosmanGroupware ConsultantCommented:
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
steve_bagnallCommented:
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
OliverAnthony_LohriAuthor Commented:
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
steve_bagnallCommented:
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

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
qwaleteeCommented:
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
steve_bagnallCommented:
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
OliverAnthony_LohriAuthor Commented:
I am actually trying to implement the code.... I will post my progress here. Steve thanks a lot!!!!
0
qwaleteeCommented:
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
OliverAnthony_LohriAuthor Commented:
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
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.