Solved

Import Excel spreadsheet into Lotus Notes to Generate Mass Mailing

Posted on 2008-06-13
6
5,776 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
  • 3
  • 2
6 Comments
 
LVL 22

Accepted Solution

by:
mbonaci earned 250 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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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 250 total points
ID: 21787286
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I thought it will be a good idea to make a post as it will help in case someone else faces these issues. I trust this gives an idea how each entry in Notes.ini can mean a lot for the Domino Server to be functioning properly. This article discusses t…
This article covers general Notes 8.5 troubleshooting information including recreating the Notes\Data folder.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

705 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now