Problem with counting characters VBA

[DJ MC]

Hi,
ive become stumped in VBA and need some help!
ive written code to export a list of email addresses from a database
and now have them in a excel sheet all down column A.

What i now need to do is put each email address in a new outlook
message, the problem is outlook only allows 20,000 characters to be put
in the To: section, i need to somehow (best way i can see it being done
maybe?) export the email addresses into notepad, add an ; after each
email and make sure the character count doesnt go over 1950, once
reached 1950 the process is looped to add other email addresses in
another mail message.

any help much appreciated

 

[DJ MC]

cheers for the reply,

Given me a good ground to work on, thanks

 

[DJ MC]

Still having many problems with this, i cant seem to output the email
addresses and where would be the best palce to output them concidering
it would need to count the characters, hit 1950 and create a new file
to output other email addresses?

Here is where ive got up to on trying to print the cell contents into
the To: and looping to the next cell and printing the contents ect ect
(which i know is wrong!)

Dim Rng As Range
Dim Cell_Contents As String
Dim SaveEmail As String
Dim Emails As String

Do While Len(Rng) < 1950

Set Rng = Range(Cells(1, 1), Cells(1, 1).End(xlDown))
For Each Cell In Rng
Cell_Contents = Worksheets("EmailAddresses").Range(Cell).Value & ";"

Set OLapp = CreateObject("Outlook.application")
Set oMailItem = OLapp.CreateItem(0)
With oMailItem

.To = Emails
.CC = ""
.Subject = "Online Payslips Now Ready"
.Body = "[Message Here]"

End With
Set OLapp = Nothing
Set oMailItem = Nothing

Print Emails, Cell_Contents
If Len(Emails) > 1950 Then

SaveEmail = Environ("HomeDrive") & Environ("HomePath") &
"\Desktop\Online Payslips Ready.msg"
oMailItem.SaveAs SaveEmail

could you adivse me where im going totally wrong?

thanks

 

[DJ MC]

Ive managed to crack this just at the last hurdle now of creating
the emails,

ive got the code to open a new mail message and output the email
addresses in the to:, but when the email saves to the desktop and the
process is done again, it seems that the 2nd mail message is
overwriting the 1st even thought its a different file name

Character_Length = Len(Cell_Contents)
Counter = Counter + Character_Length

If Counter > 19000 Then

With oMailItem

.To = Output
.Subject = "Online Payslips Now Ready"
.Body = "[Message]"
.Save (SaveEmail)

End With

Counter = 0
Character_Length = 0
EmailCount = EmailCount + 1
Output = ""

this is the code to open new mail message and put addresses in, can
anyone help me?