Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Import Excel spreadsheet into Lotus Notes to Generate Mass Mailing

Posted on 2008-06-13
6
Medium Priority
?
5,840 Views
Last Modified: 2013-12-18
I want to take a contact list developed in Excel and import the email addresses into a group in Lotus Notes and then do a mass email to everyone in the group.  How do I do this?  I know I can set the group up under BCC and it will only show one contact.  I don't want everyone to see all of the recipients of the email.  Also, is there an easy way to personalize the body of the email to include the person's name, from the contact list?
0
Comment
Question by:sfrisk
[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
  • 3
  • 2
6 Comments
 
LVL 22

Accepted Solution

by:
mbonaci earned 1000 total points
ID: 21778860
I've found this in my code library, you can modify it to fit your needs...

Improved version of Import Excel code posted by Cat Kat. This code imports any Excel files in a Notes database without modifing the code itself because it reads fields names directly from the Excel file. If the field names are are specified in the first row's cells, the code reads them and uses them as fields names for the next row's cells. The code works for maximum of 100 columns or fields.

The script prompts for:
Excel file name's full path [default : "c:\import.xls"]
Row number to import - [default :10] ( useful for importing a sample of data)
Software version -[default : W2000]
View name - [default : "Import"] (It shows some database's view names)
Form name - [default :"Import"] (It shows some database's form names)

Requirements :
The Excel files must have Notes field names specified as columns in the first row.

The code assumes default values for Excel file location, database view and form, as shown above. If the database has its own views and forms it's possible to use them. For convenience, the input box shows some of these elements' names.
Sandbox sample submitted by silvio savaresi

http://www-10.lotus.com/ldd/sandbox.nsf/ecc552f1ab6e46e4852568a90055c4cd/5a58355f6caea82b85256c95006b44a4?OpenDocument&Highlight=0,excel
Sub Initialize
		 ' revision ver 2 -30/11/02
		 ' importing  into a notes database excel specified files in specified view and form.
		 ' default value for excel file : c:\Import.xls 
		 ' default name for view and form : Import
		 ' default sw version w2000
		 
		 Dim session As New NotesSession
		 Dim db As NotesDatabase
		 Dim view As NotesView
		 Dim doc As NotesDocument
		 Set db = session.CurrentDatabase
		 Set doc = New NotesDocument(db)
		 Dim item As NotesItem
		 Dim One As String		 
		 Dim row As Integer
		 Dim written, records,ver  As Integer		 
		 Dim FName As String
		 Dim VName As String
		 Dim xlFilename As String		 
		 On Error Goto Error_call
		 
		 '  File name 
		 
		 ans$ = Inputbox$("Insert excel file path ","Info","c:\Import.xls")
		 If  ans$ = ""  Then
		 		 xlFilename = "c:\import.xls" ' This is the default name of the Excel file that will be imported
		 Else
		 		 xlFilename = ans$ ' This is the name of the Excel file that will be imported
		 End If
		 
		 ' Row number to import
		 ans$ = Inputbox$("How many rows to import ? ","Info","10")
		 If  ans$ = ""  Then
		 		 records= "10" ' This is the default import row number
		 Else
		 		 records = ans$ ' This is the user import row number
		 End If
		 
		 ' version
		 ' Row number to import
		 ans$ = Inputbox$("Are you using W2000 ? ","Info","Y")
		 If  ans$ = "n"  Or ans$ = "N" Then
		 		 ver= 8
		 Else
		 		 ver=9
		 End If
		 
		 
		 ' View name		 
		 Forall v In db.views
		 		 VName= Fname  + v.Name + "  " 
		 End Forall
		 ans$ = Inputbox$("Insert view name : "+ VName , "Info ","Import" )		 
		 If  ans$ = ""  Then
		 		 Set view = db.GetView("Import")  ' This is the default View name 
		 Else
		 		 Set view = db.GetView(ans$ ) ' This is the view name 
		 End If
		 
		 '   Form name 		 
		 Forall form In db.Forms
		 		 FName= Fname  + form.Name + "  " 
		 End Forall
		 ans$ = Inputbox$("Insert a Form name : "+ Fname , "Info","Import")		 
		 If  ans$ = ""  Then
		 		 FormName= "Import"   ' This is the default form name 
		 Else
		 		 FormName= ans$ ' This is the form name 
		 End If
		 
   'Next we connect to Excel and open the file. Then start pulling over the records.
		 Dim Excel As Variant
		 Dim xlWorkbook As Variant
		 Dim xlSheet As Variant
		 Print "Connecting to Excel..."
		 If ver =9 Then
		 		 Set Excel = CreateObject( "Excel.Application.9" ) ' for windows2000
		 Else
		 		 Set Excel = CreateObject( "Excel.Application.8" ) ' for windows95
		 		 
		 End If
		 
		 'Excel.Visible = False ' Don't display the Excel window
		 Print "Opening " & xlFilename & "..."
		 Excel.Workbooks.Open xlFilename ' Open the Excel file
		 Set xlWorkbook = Excel.ActiveWorkbook
		 Set xlSheet = xlWorkbook.ActiveSheet
		 
      ' Cycle through the rows of the Excel file, pulling the data over to Notes
		 Goto Records
		 Print "Disconnecting from Excel..."
		 xlWorkbook.Close False 'Close the Excel file without saving (we made no changes)
		 Excel.Quit 'Close Excel
		 Set Excel = Nothing ' Free the memory that we'd used
		 Print " " ' Clear the status line		 
		 
Records:
		 row = 0 'These integers intialize to zero anyway
		 written = 0
		 
		 Print "Starting import from Excel file..."
		 
		 Do While True 
Finish:		 		 
		 		 With xlSheet
		 		 		 row = row + 1		 		 
		 ' field definitions
		 		 		 If row=1 Then
		 		 		 		 temp1=.Cells( row, 1 ).Value
		 		 		 		 temp2=.Cells( row, 2 ).Value
		 		 		 		 temp3=.Cells( row, 3 ).Value
		 		 		 		 temp4=.Cells( row, 4 ).Value
		 		 		 		 temp5=.Cells( row, 5 ).Value
		 		 		 		 temp6=.Cells( row, 6 ).Value
		 		 		 		 temp7=.Cells( row, 7 ).Value
		 		 		 		 temp8=.Cells( row, 8 ).Value		 
		 		 		 		 temp9=.Cells( row, 9 ).Value
		 		 		 		 temp10=.Cells( row, 10 ).Value
		 		 		 		 temp11=.Cells( row, 11 ).Value
		 		 		 		 temp12=.Cells( row, 12 ).Value
		 		 		 		 temp13=.Cells( row, 13).Value
		 		 		 		 temp14=.Cells( row, 14).Value
		 		 		 		 temp15=.Cells( row, 15 ).Value
		 		 		 		 temp16=.Cells( row, 16 ).Value
		 		 		 		 temp17=.Cells( row, 17 ).Value		 		 		 		 
		 		 		 		 temp18=.Cells( row, 18 ).Value
		 		 		 		 temp19=.Cells( row, 19 ).Value
		 		 		 		 temp20=.Cells( row, 20 ).Value
		 		 		 		 temp21=.Cells( row, 21 ).Value
		 		 		 		 temp22=.Cells( row, 22 ).Value
		 		 		 		 temp23=.Cells( row, 23 ).Value
		 		 		 		 temp24=.Cells( row, 24 ).Value		 		 		 		 
		 		 		 		 temp25=.Cells( row, 25 ).Value		 		 		 		 
		 		 		 		 temp26=.Cells( row, 26 ).Value		 		 		 
		 		 		 		 temp27=.Cells( row, 27 ).Value
		 		 		 		 temp28=.Cells( row, 28 ).Value
		 		 		 		 temp29=.Cells( row, 29 ).Value
		 		 		 		 temp30=.Cells( row, 30 ).Value
		 		 		 		 temp31=.Cells( row, 31 ).Value
		 		 		 		 temp32=.Cells( row, 32 ).Value
		 		 		 		 temp33=.Cells( row, 33 ).Value
		 		 		 		 temp34=.Cells( row, 34 ).Value
		 		 		 		 temp35=.Cells( row, 35 ).Value
		 		 		 		 temp36=.Cells( row, 36 ).Value
		 		 		 		 temp37=.Cells( row, 37 ).Value
		 		 		 		 temp38=.Cells( row, 38 ).Value
		 		 		 		 temp39=.Cells( row, 39 ).Value
		 		 		 		 temp40=.Cells( row, 40 ).Value
		 		 		 		 temp41=.Cells( row, 41 ).Value
		 		 		 		 temp42=.Cells( row, 42 ).Value
		 		 		 		 temp43=.Cells( row, 43 ).Value
		 		 		 		 temp44=.Cells( row, 44 ).Value
		 		 		 		 temp45=.Cells( row, 45 ).Value
		 		 		 		 temp46=.Cells( row, 46 ).Value
		 		 		 		 temp47=.Cells( row, 47 ).Value
		 		 		 		 temp48=.Cells( row, 48 ).Value
		 		 		 		 temp49=.Cells( row, 49 ).Value
		 		 		 		 temp50=.Cells( row, 50 ).Value
		 		 		 		 temp51=.Cells( row, 51 ).Value
		 		 		 		 temp52=.Cells( row, 52 ).Value
		 		 		 		 temp53=.Cells( row, 53 ).Value
		 		 		 		 temp54=.Cells( row, 54 ).Value
		 		 		 		 temp55=.Cells( row, 55 ).Value
		 		 		 		 temp56=.Cells( row, 56 ).Value
		 		 		 		 temp57=.Cells( row, 57 ).Value
		 		 		 		 temp58=.Cells( row, 58 ).Value
		 		 		 		 temp59=.Cells( row, 59 ).Value
		 		 		 		 temp60=.Cells( row, 60 ).Value
		 		 		 		 temp61=.Cells( row, 61 ).Value
		 		 		 		 temp62=.Cells( row, 62 ).Value
		 		 		 		 temp63=.Cells( row, 63 ).Value
		 		 		 		 temp64=.Cells( row, 64 ).Value
		 		 		 		 temp65=.Cells( row, 65 ).Value
		 		 		 		 temp66=.Cells( row, 66 ).Value
		 		 		 		 temp67=.Cells( row, 67 ).Value
		 		 		 		 temp68=.Cells( row, 68 ).Value
		 		 		 		 temp69=.Cells( row, 69 ).Value
		 		 		 		 temp70=.Cells( row, 70 ).Value
		 		 		 		 temp71=.Cells( row, 71 ).Value
		 		 		 		 temp72=.Cells( row, 72 ).Value
		 		 		 		 temp73=.Cells( row, 73 ).Value
		 		 		 		 temp74=.Cells( row, 74 ).Value
		 		 		 		 temp75=.Cells( row, 75 ).Value
		 		 		 		 temp76=.Cells( row, 76 ).Value
		 		 		 		 temp77=.Cells( row, 77 ).Value
		 		 		 		 temp78=.Cells( row, 78 ).Value
		 		 		 		 temp79=.Cells( row, 79 ).Value
		 		 		 		 temp80=.Cells( row, 80 ).Value
		 		 		 		 temp81=.Cells( row, 81 ).Value
		 		 		 		 temp82=.Cells( row, 82 ).Value
		 		 		 		 temp83=.Cells( row, 83 ).Value
		 		 		 		 temp84=.Cells( row, 84 ).Value
		 		 		 		 temp85=.Cells( row, 85 ).Value
		 		 		 		 temp86=.Cells( row, 86 ).Value
		 		 		 		 temp87=.Cells( row, 87 ).Value
		 		 		 		 temp88=.Cells( row, 88 ).Value
		 		 		 		 temp89=.Cells( row, 89 ).Value
		 		 		 		 temp90=.Cells( row, 90 ).Value
		 		 		 		 temp91=.Cells( row, 91 ).Value
		 		 		 		 temp92=.Cells( row, 92 ).Value
		 		 		 		 temp93=.Cells( row, 93 ).Value
		 		 		 		 temp94=.Cells( row, 94 ).Value
		 		 		 		 temp95=.Cells( row, 95 ).Value
		 		 		 		 temp96=.Cells( row, 96 ).Value
		 		 		 		 temp97=.Cells( row, 97 ).Value
		 		 		 		 temp98=.Cells( row, 98 ).Value
		 		 		 		 temp99=.Cells( row, 99 ).Value
		 		 		 		 temp100=.Cells( row, 100 ).Value
		 		 		 End If		 		 		 
' end field definition
		 		 		 
		 		 '		 Set view = db.GetView("Import")
		 		 		 Set doc = db.CreateDocument 'Create a new doc 
		 		 		 doc.Form = FormName
		 		 		 
'...set value of doc...
		 		 		 
		 		 		 Set item = doc.ReplaceItemValue( temp1, .Cells( row, 1 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp2, .Cells( row, 2 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp3, .Cells( row, 3 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp4, .Cells( row, 4 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp5, .Cells( row, 5 ).Value )		 		 		 
		 		 		 Set item = doc.ReplaceItemValue( temp6, .Cells( row, 6 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp7, .Cells( row, 7 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp8, .Cells( row, 8 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp9, .Cells( row, 9 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp10, .Cells( row, 10 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp11, .Cells( row, 11 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp12, .Cells( row, 12 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp13, .Cells( row, 13 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp14, .Cells( row, 14 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp15, .Cells( row, 15 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp16, .Cells( row, 16 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp17, .Cells( row, 17 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp18, .Cells( row, 18 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp19, .Cells( row, 19 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp20, .Cells( row, 20 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp21, .Cells( row, 21 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp22, .Cells( row, 22 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp23, .Cells( row, 23 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp24, .Cells( row, 24 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp25, .Cells( row, 25 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp26, .Cells( row, 26 ).Value )
 
		 		 		 Set item = doc.ReplaceItemValue( temp27, .Cells( row, 27 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp28, .Cells( row, 28 ).Value )		 		 		 
		 		 		 Set item = doc.ReplaceItemValue( temp29, .Cells( row, 29 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp30, .Cells( row, 30 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp31, .Cells( row, 31 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp32, .Cells( row, 32 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp33, .Cells( row, 33 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp34, .Cells( row, 34 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp35, .Cells( row, 35 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp36, .Cells( row, 36 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp37, .Cells( row, 37 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp38, .Cells( row, 38 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp39, .Cells( row, 39 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp40, .Cells( row, 40 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp41, .Cells( row, 41 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp42, .Cells( row, 42 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp43, .Cells( row, 43 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp44, .Cells( row, 44 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp45, .Cells( row, 45 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp46, .Cells( row, 46 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp47, .Cells( row, 47 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp48, .Cells( row, 48 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp49, .Cells( row, 49 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp50, .Cells( row, 50 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp51, .Cells( row, 51 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp52, .Cells( row, 52 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp53, .Cells( row, 53 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp54, .Cells( row, 54).Value 
)		 
		 		 		 Set item = doc.ReplaceItemValue( temp55, .Cells( row, 55 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp56, .Cells( row, 56 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp57, .Cells( row, 57 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp58, .Cells( row, 58 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp59, .Cells( row, 59 ).Value )		 
		 		 		 Set item = doc.ReplaceItemValue( temp60, .Cells( row, 60 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp61, .Cells( row, 61 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp62, .Cells( row, 62 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp63, .Cells( row, 63 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp64, .Cells( row, 64 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp65, .Cells( row, 65 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp66, .Cells( row, 66 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp67, .Cells( row, 67 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp68, .Cells( row, 68 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp69, .Cells( row, 69 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp70, .Cells( row, 70 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp71, .Cells( row, 71 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp72, .Cells( row, 72 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp73, .Cells( row, 73 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp74, .Cells( row, 74 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp70, .Cells( row, 70 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp71, .Cells( row, 71 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp72, .Cells( row, 72 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp73, .Cells( row, 73 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp74, .Cells( row, 74 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp75, .Cells( row, 75 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp76, .Cells( row, 76 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp77, .Cells( row, 77 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp78, .Cells( row, 78 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp79, .Cells( row, 79 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp80, .Cells( row, 80 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp81, .Cells( row, 81 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp82, .Cells( row, 82 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp83, .Cells( row, 83 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp84, .Cells( row, 84 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp85, .Cells( row, 85 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp86, .Cells( row, 86 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp87, .Cells( row, 87 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp88, .Cells( row, 88 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp89, .Cells( row, 89 ).Value )		 		 		 
		 		 		 Set item = doc.ReplaceItemValue( temp90, .Cells( row, 90 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp91, .Cells( row, 91 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp92, .Cells( row, 92 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp93, .Cells( row, 93 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp94, .Cells( row, 94 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp95, .Cells( row, 95 ).Value )
		 		 		 Set item = doc.ReplaceItemValue
( temp96, .Cells( row, 96 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp97, .Cells( row, 97 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp98, .Cells( row, 98 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp99, .Cells( row, 99 ).Value )
		 		 		 Set item = doc.ReplaceItemValue( temp100, .Cells( row, 100 ).Value )
		 		 		 
		 		 		 
		 		 		 Call doc.Save( True, True ) 'Save the new doc
		 		 		 
		 		 		 written = written + 1
		 		 		 Print Str(written)
		 		 		 If written =Int(records) Then
		 		 		 		 Goto Done
		 		 		 End If
		 		 		 
		 		 End With
		 Loop
		 Return
		 Goto Done
Error_call:
		 Messagebox "Check Excel file location or SW Version or View Name or Form Name" ,MB_OK+MB_ICONINFORMATION,"Warning ! "
		 Print " " ' Clear the status line		 
		 Exit Sub
		 
Done:
		 Excel.Quit 'Close Excel
		 Set Excel = Nothing ' Free the memory that we'd used
		 Print "Imported " +Cstr(row) + " rows" ' Clear the status line		 
End Sub

Open in new window

0
 
LVL 22

Expert Comment

by:mbonaci
ID: 21778884
This one is even better:

It's from here:
http://www.experts-exchange.com/Applications/Email/Lotus_Notes_Domino/Q_21712598.html


Hope this helps,
mb¤
'Import Excel: 
 
Option Public
Option Declare
 
'Important set these values to your environment...
Const str_VIEWNAME="edv"
Const str_IMPORTFILE="C:\payrollinput.xls"
Const max_RECORDS=10	'Set this to the maximum number of records you want to loop
Const max_SKIPROWS=10	'Set this to the number of blank rows you want to skip before you assume
				'the rest of the worksheet is blank
Sub Initialize
	'Clear Lotus Notes View Of Employee Records
	Dim session As New NotesSession
	Dim db As NotesDatabase
	Dim view As NotesView
	Dim vColl As NotesViewEntryCollection
	Set db = session.currentdatabase
	Set view = db.getview(str_VIEWNAME)
	If Not view Is Nothing Then
		Set vColl = view.allentries
		If vColl.count>0 Then
			Call vcoll.removeAll(True)		
		End If
		If Not vColl Is Nothing Then Set vColl = Nothing
	End If
 
	Dim xlFilename As String
	'IMPORT FILENAME
	xlFilename= str_IMPORTFILE
 
	Dim doc As NotesDocument
	Dim row As Integer
	Dim written As Integer
 
	On Error Goto Handle_Error
	'First Connect to Excel and see if there are any records to import
	Dim Excel As Variant
	Dim xlWorkbook As Variant
	Dim xlSheet As Variant
	Print "Connecting to Excel..."
	Set Excel = CreateObject( "Excel.Application" )
	If Excel Is Nothing Then
		Print "Unable to open the excel object..."
		Exit Sub
	End If
 
	Excel.Visible = False '// Don't display the Excel window
	Excel.Workbooks.Open xlFilename '// Open the Excel file
	Print "Opening " & xlFilename & "..."
	Excel.Workbooks.add
	Excel.Workbooks(1).Worksheets(1).Activate
	Set xlWorkbook = Excel.Workbooks(1)
	Set xlSheet = Excel.Workbooks(1).Worksheets(1)
	Dim chk As String, skiprow As Integer
 
	Set view= db.GetView("edv")
	If view Is Nothing Then
		Print "Unable to locate edv view..."
		Exit Sub
	End If
 
Records:
	row = 1 '// These integers intialize to zero anyway
	written = 0
	Print "Starting import from Excel file..."
	chk="xxxx"
 
	Do While (row<=max_RECORDS)
		With xlSheet
			chk =Trim(Format(.cells(row,1).value) + _
			Format(.cells(row,2).value) + _	
			Format(.cells(row,3).value) + _
			Format(.cells(row,4).value))
			If Len(chk)<0 Then
				skiprow = skiprow +1
				Goto NextRow
			End If
			Select Case row
			Case 1
				'Skip header row
			Case Else
				If Len(chk)>0 Then
					Set doc = New NotesDocument(db)
					doc.Form = "EmpDetails" 'Form name
					'Update to Fields
					doc.employee_number = .Cells(row, 1).Value
					doc.employee_lastname = .Cells( row, 2).Value
					doc.employee_firstname = .Cells( row, 3).Value
					doc.employee_middlename = .Cells(row, 4).Value
					doc.Save True, False, True
					Print " Creating Record..." + Cstr(row)
					written = written +1
					set doc = nothing
					'If we find a record, then set the skiprow back to 0 - it was just a blank row
					skiprow = 0
				End If
			End Select
		End With
NextRow:
		row= row+1
		If skiprow =>max_SKIPROWS Then
			Print "Ten blank rows reached... assuming the rest of the worksheet is blank..."
			Exit Do
		End If
	Loop
 
	Print "Disconnecting from Excel..."
	Excel.activeworkbook.close
	Excel.Quit '// Close Excel
	Set Excel = Nothing '// Free the memory that we'd used
	view.Refresh
	Exit Sub
 
Handle_Error:
	If Err = 213 Then
		Print "Import File not found..."
		Err = 0
		Exit Sub
	End If
	On Error Goto 0
	If Not xlworkbook Is Nothing Then
		Excel.activeworkbook.close        '// Close the Excel file without saving (we made no changes)
		Excel.Quit '// Close Excel
		Set Excel = Nothing '// Free the memory that we'd used
	End If
	Exit Sub
End Sub

Open in new window

0
 
LVL 63

Expert Comment

by:SysExpert
ID: 21780426
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Author Comment

by:sfrisk
ID: 21781364
I'm confused.  I dont' think I asked the question correctly.  Can I import the names, addresses and emails into my address book in Lotus Notes from the spreadsheet?  After I do that, can I insert the first name of the person in the body of each email, but send them all out at one time?  The fields to import are listed below.  Actually, all I need would be first name and email to be imported in the address book.

First Name      Middle Initial      Last Name      Last Name Suffix      Title      Company 1      Company 2
Address 1                      Address 2           Email Address      City      State    Province      Postal Code      Country      Phone      Fax
0
 
LVL 63

Expert Comment

by:SysExpert
ID: 21787273
1) You do not need to use your address book, but you can.

2) Plenty of routines that will allow you to send email, personalized . It would be easiest to set up a database with a Form for the fileds you want to change. This allows you more control, especially if going to Notes users.

So it really depends on  how much personalization you want.


I hope this helps !
0
 
LVL 63

Assisted Solution

by:SysExpert
SysExpert earned 1000 total points
ID: 21787286
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

For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …

618 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