Mass Mailing from excel using Lotus Notes

I have pieced this together and can not get it to work.  Can anyone look at this code and tell me what I am missing.  I have a spreadhsheet (sheet1) with 3 columns.  I need to send out one email to all of the addresses in the list.  How do I get it to do this :(

Sub Button8_Click()
Dim session As Object
Dim db As Object
Dim doc As Object
Dim maildoc As Object
Dim rtitem As Object
Dim maildb As Object
Set session = CreateObject("Notes.NotesSession")
Set db = session.GetDatabase("", "mail\a_PARKES.nsf")
    For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" Then
            Set maildoc = maildb.CreateDocument
            With maildoc
            .Form = "Memo"
            .BlindCopyTo = Split(BCC, ",")
            End With
        End If
      Next cell
Call doc.Send(False)
Set session = Nothing
Set db = Nothing
Set doc = Nothing
End Sub
ThePROXAsked:
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.

SysExpertCommented:
It looks like you may be trying to use an Archive Mail DB rather tan your normal Maul DB.

In most cases mail\a_PARKES.nsf") , demotes an archive. Your mail db is probably parkes.nsf.

I assume that you have the Excel spreadsheet open when this is run ?

I hope this helps !
0
SysExpertCommented:
Also, I do not see any to fileld being set, just a bcc. Additionally, where is the Body and or subject ?

I hope this helps !
0
SysExpertCommented:
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

ThePROXAuthor Commented:
If we could go from scratch.. This is what I am trying to do.  I have a list of emails (about 300 or so).  I want to send out an email to all of them.  I want to take the names that are in that list and put them into the BCC field.  The layout is like such:

Name    |   Email Address   |  Email?

bob       |   bob@bob.com   |  Yes

So on.  I want a button that you click that will take all of the emails (or the ones marked yes) and put the email addresses into the BCC field.  If there is a better way to do that than the garbage that I was using above, please post.

Thanks,

Shadd
0
SysExpertCommented:
There aer certainly better methods to do this.

A few questions first.

1) How often does your list change ?

2) there may be a linit of about 32K for the bcc field, so it may require 2 emails, rather than one.

3) How often does this need to be sent ?

4) Who is doing the sending ( yourself, a non- technical user ... ??)

If the list does not change often, I would suggest simply using Excel VBA to put the list into a single doc ( notepad ), and simply copy all of them into the Bcc field either manually or via script.

I hope this helps !
0
ThePROXAuthor Commented:
The list could change about once a month.

2)  Don't really care if it takes 2

3)  Maybe once per month

4)  non-technical (thus why I just want a button that they click)

Any code would be appreciated.
0
cezarFCommented:
..also maildb is not initialized thus "Set maildoc = maildb.CreateDocument" will not work.

try change the following:
1. Set db = session.GetDatabase("", "mail\a_PARKES.nsf") ---> Set db = session.GetDatabase("", "mail\PARKES.nsf") '( note the db name as suggested by SysExpert)
2. Set maildoc = maildb.CreateDocument --> Set maildoc = db.CreateDocument

0
marilyngCommented:
The excel object is not instantiated either :)

Check here for example: http://searchdomino.techtarget.com/tip/1,289483,sid4_gci822343,00.html?track=NL-47&ad=541560
0
SysExpertCommented:
Well it looks like a combination of VBA and LS. I am fluent in both. Let me think until tomorrow on which will be easier to use to implement what you need.

0
Sjef BosmanGroupware ConsultantCommented:
Hi ThePROX,

>           .BlindCopyTo = Split(BCC, ",")

Where did you give BCC a value?

Cheers!
0
SysExpertCommented:
ThePROX
OK , It i time to get more info.

1) Where is the body of the email you are trying to send.

2) It looks you like you are trying to create the button in Excell VBA to Call Notes and send out something to each user lisetd in your Excel sheet.

3) I do not see the body of the mail defined anywhere

4) There are erros in your VBA code.
Delete the Dim DOc.

change Call doc.Send(False) to Call maildoc.Send(False)
and also make the corrections that cezarF mentioned.

I hope this helps !
0
ThePROXAuthor Commented:
To above questoin:

1) I want to be able to just type in a msg and subject line of what ever I want.  I just want this to import the email addresses into the BCC field.

This is what I have now.... It is still not working... what do I need to do now?

Sub Button7_Click()
Dim session As Object
Dim db As Object
Dim maildoc As Object
Dim rtitem As Object
Dim maildb As Object
Set session = CreateObject("Notes.NotesSession")
Set db = session.GetDatabase("", "mail\PARKES18.nsf")
    For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
        If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" Then
            Set maildoc = db.CreateDocument
            With maildoc
            .Form = "Memo"
            .BlindCopyTo = Split(BCC, ",")
            End With
        End If
      Next cell
Call maildoc.Send(False)
Set session = Nothing
Set db = Nothing
Set doc = Nothing
End Sub
0
cezarFCommented:
try this..

Sub test()
    Dim session As Object
    Dim db As Object
    Dim maildoc As Object
    Dim rtitem As Object
    Dim maildb As Object
    Set session = CreateObject("Notes.NotesSession")
    Set db = session.GetDatabase("", "mail\PARKES18.nsf")
        For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
            If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" Then
                Set maildoc = db.CreateDocument
                With maildoc
                    .Form = "Memo"
                    .SendTo = cell.Value
                    .Subject = "Your Subject"
                    .Body = "Your Mail Body"
                    '.BlindCopyTo = Split(BCC, ",")
                End With
                Call maildoc.Send(False)
            End If
        Next cell
    Set session = Nothing
    Set db = Nothing
    Set doc = Nothing
End Sub
0
cezarFCommented:
oooops. I misunderstood. the code below sends 1 mail with all recipients in bcc whereas the one in my previoius comment sends n mails to n recipients

    Dim session As Object
    Dim db As Object
    Dim maildoc As Object
    Dim rtitem As Object
    Dim maildb As Object
   
    Dim bccArr() As String
   
    Set session = CreateObject("Notes.NotesSession")
    Set db = session.GetDatabase("", "mail\PARKES18.nsf")
        ReDim bccArr(0)
        For Each cell In Sheets("Sheet1").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
            If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "yes" Then
                If bccArr(UBound(bccArr)) <> "" Then
                    ReDim Preserve bccArr(UBound(bccArr) + 1)
                End If
                bccArr(UBound(bccArr)) = cell.Value
            ElseIf Trim(cell.Value) = "" Then
                Exit For
            End If
        Next cell
        Set maildoc = db.CreateDocument
        With maildoc
            .Form = "Memo"
            '.SendTo = ""
            .Subject = "Your Subject"
            .Body = "Your Mail Body"
            .BlindCopyTo = bccArr
        End With
        Call maildoc.Send(False)
    Set session = Nothing
    Set db = Nothing
    Set doc = Nothing
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
marilyngCommented:
Still missing excel object... not instantiated..
0
cezarFCommented:
marilyng,  he instantiated the session by Set session = CreateObject("Notes.NotesSession"). I assume he's using some sort of an excel macro... tested it in excel and it worked.
0
marilyngCommented:
Oops, sorry, didn't realize he was going from excel to Notes.. thought it was the other way 'round... nevermind ;)
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.