Mailmerge in Lotus from csv/AccessDB/Excel

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

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.

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

I love script, but File/Import... is A LOT easier for a lazy bum like me :)

Not easier when you don't have to write it ;)

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 ;)

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.

OliverAnthony_Lohri,

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

Sjef

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

ah and by the way...I do not have Lotus 123

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!

Lotus 1-2-3? Neither do I :) Excel can save to Lotus 1-2-3 format, that's just what you need.

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

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!!!!

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

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.

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

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

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

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.

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

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

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.

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.

I am actually trying to implement the code.... I will post my progress here. Steve thanks a lot!!!!

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

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