Adding outlook.recipient values to an Access table

[CurtP]

Hi,

I am working on a function to identify outlook email recipients that wont render, and put them into a table.
I am new to VBA, and know how to simply insert records into a table. But this kind of variable is getting me stuck.
The variable .To contains the whole list of "email recipients"

The variable objOutlookRecip.Name is what I want to insert into a table called "BadEmailIDsTbl" which is just 1 column.
But I cant get an INSERT qry statement to work with that variable name in the loop.
Is AddNew an option? I just cant get the syntax right

Another thing I tried was to take the function name "checkAllEmails" and use that with in insert qry at the end because that just contains all the "bad" email values
You can INSERT attempt at the bottom of my screenshot, but that gives me a 3035 system resource exceeded error. Def. misleading because it should only be about 4000 records its trying to insert.

 

[CurtP]

Im thinking the error really is a data mismatch type as my access table is set up for Short Text type data. I tried setting objOutlookRecip.Name to CStr(objOutlookRecip.Name) with no success

 

[Gasman]

I would split all the emails by the ; character, or any others that can be used.
Then add them one by one.

I think that SQL is useless?. Just use the Variable if you are going to keep them all together.
Something along the lines of

strSQL = "INSERT INTO BadEmailsTable '" & CheckAllEmails & "'"
Debug.Print strSQL
'db.Execute strSQL

Uncomment the db.execute when the sql string is correct.

 

[CurtP]

Hey, just gave it a shot.

Same thing. It printed all the bad email values to the immediate window, but gave me an immediate System resource error when I ran the db execute..

The function checkAllEmails returns a string value (or is supposed to), but im wondering if the line checkAllEmails = checkAllEmails & objOutlookRecip.Name & vbCrLf somewhow makes checkAllEmails a variable that I cant insert into an access table

With objOutlookMsg
    .To = Left(emailstring, Len(emailstring) - 1)
    For Each objOutlookRecip In .Recipients
        If objOutlookRecip.Name = "" Or objOutlookRecip.Name = vbNullString Then GoTo Skip
        If Not validEmailFormat(objOutlookRecip.Name) Then
            'objOutlookRecip.Name = CStr(objOutlookRecip.Name)
            checkAllEmails = checkAllEmails & objOutlookRecip.Name & vbCrLf
            Counter = Counter + 1
            GoTo Skip
        End If
        objOutlookRecip.Resolve
        If Not objOutlookRecip.Resolved Then
            checkAllEmails = checkAllEmails & objOutlookRecip.Name & vbCrLf
            Counter = Counter + 1
        End If
Skip:
    Next
End With
        StrSql = "INSERT INTO BadEmailIDsTable '" & checkAllEmails & "'"
        Debug.Print StrSql
       db.Execute StrSql
DoCmd.Hourglass False
If Counter > 0 Then
    If Counter = 1 Then
        checkAllEmails = "The following email address could not be resolved for [" & qryName & "].[" & EmailField & "]" & vbCrLf & vbCrLf & checkAllEmails
    Else
        checkAllEmails = "The following " & Counter & " email addresses could not be resolved for [" & qryName & "].[" & EmailField & "]" & vbCrLf & vbCrLf & checkAllEmails

    End If
    MsgBox checkAllEmails
Else


    MsgBox "All email addresses resolved for [" & qryName & "].[" & EmailField & "]"
End If
End Function

 

[CurtP]

I would split all the emails by the ; character, or any others that can be used.
Then add them one by one.

I think that SQL is useless?. Just use the Variable if you are going to keep them all together.
Something along the lines of

strSQL = "INSERT INTO BadEmailsTable '" & CheckAllEmails & "'"
Debug.Print strSQL
'db.Execute strSQL

Uncomment the db.execute when the sql string is correct.

 

[June7]

Please define "render" in this context.

If you are trying to input a string that exceeds 255 characters into a Short Text field, that will fail.

 

[CurtP]

Please define "render" in this context.

If you are trying to input a string that exceeds 255 characters into a Short Text field, that will fail.

So this was obviously prior existing code. When I say render, I mean when we send out excel attachments and generate the dtstribution lists within the outlook window, these values are unable to render to find the employee, which causses the outlook window to be hung up with a pop up and not moving on to the next email to create.

I didnt think of the 255 limit, but I think you may be right in that its trying to insert all those names in 1 spot in the table. I did try to add each name as it was going through the loop - after the If NOT validEmailFormat..... THen

but I thought Id have to convert the value it got to a string. So i dont know what id have to add here to get the invidulal name added to the table via the checkAllEMails variable before it adds the next name to the variable... ( checkAllEmails = checkAllEmails & objOutlookRecip.Name & vbCrLf)

    .To = Left(emailstring, Len(emailstring) - 1)
    For Each objOutlookRecip In .Recipients
        If objOutlookRecip.Name = "" Or objOutlookRecip.Name = vbNullString Then GoTo Skip
        If Not validEmailFormat(objOutlookRecip.Name) Then
            'objOutlookRecip.Name = CStr(objOutlookRecip.Name)
            checkAllEmails = checkAllEmails & objOutlookRecip.Name & vbCrLf
            Counter = Counter + 1
            GoTo Skip
        End If
        objOutlookRecip.Resolve
        If Not objOutlookRecip.Resolved Then
            checkAllEmails = checkAllEmails & objOutlookRecip.Name & vbCrLf
            Counter = Counter + 1
        End If

 

[Gasman]

I would not use vbcrlf but a ; as outlook does.
However I personally would add one per record.

 

[Pat Hartman]

Please don't store the bad emails as a mushed string. Everything becomes sooooooooooooooo much simpler to work with going forward if you store the bad emails in a bad email table - one email address per row. OR if you are storing the email addresses in your Access db, then flag the record with the date you last checked the email and a valid/invalid flag.