In over my head with Automation error

M

maceslin

I have modified the following code from another source and get an
error "Automation error- server threw an exception. I have done some
research online but think, make that I know I am way over my head.
Some very specific help would be appreciated.

I have active hyperlinks that I need to maintain which is why I am
emailing a form rather than sending a report using Access 2003. Other
suggegstions to send this data will be considered

Private Sub cmdEmailForm_Click()
Dim rst As DAO.Recordset
Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Dim strMessage As String

Dim strTableBeg As String
Dim strTableEnd As String
Dim strFntNormal As String
Dim strFntHeader As String
Dim strFntEnd As String
Dim strEmailSQL As String
Dim strEmailSelect As String
Dim strEmailFrom As String
Dim strEmailWhere As String

' build sql statement
strEmailSelect = "Select tblComments.solution, tblBasicData.
[Lesson IDPK], tblNumbered.Numbered_Fleet, tblStatusChoices.Status,
tblComments.Date_Entered, tblBasicData.HyperlinkToLesson,
tblDOTMLPF.DOTMLPF_Choices"
strEmailFrom = "FROM tblDOTMLPF INNER JOIN (qryLastEntry INNER
JOIN (tblStatusChoices INNER JOIN (tblNumbered INNER JOIN
(tblBasicData INNER JOIN tblComments ON tblBasicData.[Lesson IDPK] =
tblComments.Lesson_IDFK) ON tblNumbered.NumberFleetPK =
tblBasicData.NumberedFleetFK) ON tblStatusChoices.StatusChoiceIDPK =
tblComments.statusFK) ON (qryLastEntry.Lesson_IDFK =
tblComments.Lesson_IDFK) AND (qryLastEntry.MaxOfDate_Entered =
tblComments.Date_Entered) AND (qryLastEntry.DOTLMPF_ChoiceFK =
tblComments.DOTLMPF_ChoiceFK)) ON tblDOTMLPF.[DOTMLPF ID PK] =
tblComments.DOTLMPF_ChoiceFK"
strEmailWhere = " Where Numbered_Fleet=""" & Forms![frmPara1]!
[cboNumbered] & """"
strEmailSQL = strEmailSelect & " " & strEmailFrom & " " &
strEmailWhere

'Define format for output
strTableBeg = "<table border=0>"
strTableEnd = "</table>"
strFntHeader = "<font size=2 face=" & Chr(34) & "Arial" & Chr(34)
& "><b>" & _
"<tr bgcolor=lightblue>" & _
"<td nowrap>Lesson ID</td>" & _
"<td>Hyperlink to Lesson</td>" & _
"<td>DOTMLPF</td>" & _
"<td>Most recent comment</td>" & _
"<td>Status</td>" & _
"<td>Date Entered</td>" & _
"</tr></b></font>"
strFntNormal = "<font color=black face=" & Chr(34) & "Arial" &
Chr(34) & " size=1>"
strFntEnd = "</font>"



Set rst = CurrentDb.OpenRecordset(strEmailSQL)

'Build HTML Output for the DataSet
strMessage = strTableBeg & strFntNormal & strFntHeader
'need to test with Outlook
Do Until rst.EOF
strMessage = strMessage & _
"<tr>" & _
"<td>" & rst![LessonID PK] & "</td>" & _
"<td>" & rst!HyperlinkToLesson & "</td>" &
_
"<td>" & rst!DOTMLPF_Choices & "</td>" & _
"<td>" & rst!solution & "</td>" & _
"<td>" & rst!status & "</td>" & _
"<td>" & rst!Date_Entered & "</td>" & _
"</tr>"
rst.MoveNext
Loop

strMessage = strMessage & strFntEnd & strTableEnd

rst.Close
Set rst = Nothing


'Create e-mail item
Set olApp = Outlook.Application
Set objMail = olApp.CreateItem(olMailItem)
With objMail
'Set body format to HTML
.to = " "
.Subject = "Status of Information Operations submissions to
the NLLS"
' On Error Resume Next

********** the following line throws the code, everything else returns
expected value sin local
window****************************************** .

BodyFormat = olFormatHTML
.HTMLBody = "<HTML><BODY>" & strFntNormal & strMessage & " </
BODY></HTML>"
.Display
End With
End Sub

Thanks
Dave
 
S

Stuart McCall

I think that:

BodyFormat = olFormatHTML

ought to be:

..BodyFormat = olFormatHTML

Otherwise (at first reading) everything looks ok.
 
M

maceslin

I think that:

BodyFormat = olFormatHTML

ought to be:

.BodyFormat = olFormatHTML

Otherwise (at first reading) everything looks ok.
Click to expand...

It is, I did a bad job of checking my cut and paste job

Any other thoughts?
 
S

Stuart McCall

It is, I did a bad job of checking my cut and paste job

Any other thoughts?
Click to expand...

Try taking that line out. I don't think you need it. When the .HTMLBody
property is set, (IIRC) Outlook is smart enough to set it for you. Why
including it should throw an exception is beyond me, though.
 
D

Dale Fye

Dave,

You might also want to check with your Exchange administrator. Based on the
content of your HTML, I expect you are working for the military (DOTMLPF) and
I know we are blocked from sending anything other than text (no HTML).

You might also want to consider creating a report, and sending it as HTML
using the SendObject method, rather than automation.

Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.


[email protected] said:
I have modified the following code from another source and get an
error "Automation error- server threw an exception. I have done some
research online but think, make that I know I am way over my head.
Some very specific help would be appreciated.

I have active hyperlinks that I need to maintain which is why I am
emailing a form rather than sending a report using Access 2003. Other
suggegstions to send this data will be considered

Private Sub cmdEmailForm_Click()
Dim rst As DAO.Recordset
Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Dim strMessage As String

Dim strTableBeg As String
Dim strTableEnd As String
Dim strFntNormal As String
Dim strFntHeader As String
Dim strFntEnd As String
Dim strEmailSQL As String
Dim strEmailSelect As String
Dim strEmailFrom As String
Dim strEmailWhere As String

' build sql statement
strEmailSelect = "Select tblComments.solution, tblBasicData.
[Lesson IDPK], tblNumbered.Numbered_Fleet, tblStatusChoices.Status,
tblComments.Date_Entered, tblBasicData.HyperlinkToLesson,
tblDOTMLPF.DOTMLPF_Choices"
strEmailFrom = "FROM tblDOTMLPF INNER JOIN (qryLastEntry INNER
JOIN (tblStatusChoices INNER JOIN (tblNumbered INNER JOIN
(tblBasicData INNER JOIN tblComments ON tblBasicData.[Lesson IDPK] =
tblComments.Lesson_IDFK) ON tblNumbered.NumberFleetPK =
tblBasicData.NumberedFleetFK) ON tblStatusChoices.StatusChoiceIDPK =
tblComments.statusFK) ON (qryLastEntry.Lesson_IDFK =
tblComments.Lesson_IDFK) AND (qryLastEntry.MaxOfDate_Entered =
tblComments.Date_Entered) AND (qryLastEntry.DOTLMPF_ChoiceFK =
tblComments.DOTLMPF_ChoiceFK)) ON tblDOTMLPF.[DOTMLPF ID PK] =
tblComments.DOTLMPF_ChoiceFK"
strEmailWhere = " Where Numbered_Fleet=""" & Forms![frmPara1]!
[cboNumbered] & """"
strEmailSQL = strEmailSelect & " " & strEmailFrom & " " &
strEmailWhere

'Define format for output
strTableBeg = "<table border=0>"
strTableEnd = "</table>"
strFntHeader = "<font size=2 face=" & Chr(34) & "Arial" & Chr(34)
& "><b>" & _
"<tr bgcolor=lightblue>" & _
"<td nowrap>Lesson ID</td>" & _
"<td>Hyperlink to Lesson</td>" & _
"<td>DOTMLPF</td>" & _
"<td>Most recent comment</td>" & _
"<td>Status</td>" & _
"<td>Date Entered</td>" & _
"</tr></b></font>"
strFntNormal = "<font color=black face=" & Chr(34) & "Arial" &
Chr(34) & " size=1>"
strFntEnd = "</font>"



Set rst = CurrentDb.OpenRecordset(strEmailSQL)

'Build HTML Output for the DataSet
strMessage = strTableBeg & strFntNormal & strFntHeader
'need to test with Outlook
Do Until rst.EOF
strMessage = strMessage & _
"<tr>" & _
"<td>" & rst![LessonID PK] & "</td>" & _
"<td>" & rst!HyperlinkToLesson & "</td>" &
_
"<td>" & rst!DOTMLPF_Choices & "</td>" & _
"<td>" & rst!solution & "</td>" & _
"<td>" & rst!status & "</td>" & _
"<td>" & rst!Date_Entered & "</td>" & _
"</tr>"
rst.MoveNext
Loop

strMessage = strMessage & strFntEnd & strTableEnd

rst.Close
Set rst = Nothing


'Create e-mail item
Set olApp = Outlook.Application
Set objMail = olApp.CreateItem(olMailItem)
With objMail
'Set body format to HTML
.to = " "
.Subject = "Status of Information Operations submissions to
the NLLS"
' On Error Resume Next

********** the following line throws the code, everything else returns
expected value sin local
window****************************************** .

BodyFormat = olFormatHTML
.HTMLBody = "<HTML><BODY>" & strFntNormal & strMessage & " </
BODY></HTML>"
.Display
End With
End Sub

Thanks
Dave
Click to expand...
 
M

maceslin

Dave,

You might also want to check with your Exchange administrator. Based on the
content of your HTML, I expect you are working for the military (DOTMLPF) and
I know we are blocked from sending anything other than text (no HTML).

You might also want to consider creating a report, and sending it as HTML
using the SendObject method, rather than automation.

Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.



[email protected] said:
I have modified the following code from another source and get an
error "Automation error- server threw an exception. I have done some
research online but think, make that I know I am way over my head.
Some very specific help would be appreciated.
Click to expand...
I have active hyperlinks that I need to maintain which is why I am
emailing a form rather than sending a report using Access 2003. Other
suggegstions to send this data will be considered
Click to expand...
Private Sub cmdEmailForm_Click()
Dim rst As DAO.Recordset
Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Dim strMessage As String
Click to expand...
Dim strTableBeg As String
Dim strTableEnd As String
Dim strFntNormal As String
Dim strFntHeader As String
Dim strFntEnd As String
Dim strEmailSQL As String
Dim strEmailSelect As String
Dim strEmailFrom As String
Dim strEmailWhere As String
Click to expand...
' build sql statement
strEmailSelect = "Select tblComments.solution, tblBasicData.
[Lesson IDPK], tblNumbered.Numbered_Fleet, tblStatusChoices.Status,
tblComments.Date_Entered, tblBasicData.HyperlinkToLesson,
tblDOTMLPF.DOTMLPF_Choices"
strEmailFrom = "FROM tblDOTMLPF INNER JOIN (qryLastEntry INNER
JOIN (tblStatusChoices INNER JOIN (tblNumbered INNER JOIN
(tblBasicData INNER JOIN tblComments ON tblBasicData.[Lesson IDPK] =
tblComments.Lesson_IDFK) ON tblNumbered.NumberFleetPK =
tblBasicData.NumberedFleetFK) ON tblStatusChoices.StatusChoiceIDPK =
tblComments.statusFK) ON (qryLastEntry.Lesson_IDFK =
tblComments.Lesson_IDFK) AND (qryLastEntry.MaxOfDate_Entered =
tblComments.Date_Entered) AND (qryLastEntry.DOTLMPF_ChoiceFK =
tblComments.DOTLMPF_ChoiceFK)) ON tblDOTMLPF.[DOTMLPF ID PK] =
tblComments.DOTLMPF_ChoiceFK"
strEmailWhere = " Where Numbered_Fleet=""" & Forms![frmPara1]!
[cboNumbered] & """"
strEmailSQL = strEmailSelect & " " & strEmailFrom & " " &
strEmailWhere
Click to expand...
'Define format for output
strTableBeg = "<table border=0>"
strTableEnd = "</table>"
strFntHeader = "<font size=2 face=" & Chr(34) & "Arial" & Chr(34)
& "><b>" & _
"<tr bgcolor=lightblue>" & _
"<td nowrap>Lesson ID</td>" & _
"<td>Hyperlink to Lesson</td>" & _
"<td>DOTMLPF</td>" & _
"<td>Most recent comment</td>" & _
"<td>Status</td>" & _
"<td>Date Entered</td>" & _
"</tr></b></font>"
strFntNormal = "<font color=black face=" & Chr(34) & "Arial" &
Chr(34) & " size=1>"
strFntEnd = "</font>"
Click to expand...
Set rst = CurrentDb.OpenRecordset(strEmailSQL)
Click to expand...
'Build HTML Output for the DataSet
strMessage = strTableBeg & strFntNormal & strFntHeader
'need to test with Outlook
Do Until rst.EOF
strMessage = strMessage & _
"<tr>" & _
"<td>" & rst![LessonID PK] & "</td>" & _
"<td>" & rst!HyperlinkToLesson & "</td>" &
_
"<td>" & rst!DOTMLPF_Choices & "</td>" & _
"<td>" & rst!solution & "</td>" & _
"<td>" & rst!status & "</td>" & _
"<td>" & rst!Date_Entered & "</td>" & _
"</tr>"
rst.MoveNext
Loop
Click to expand...
strMessage = strMessage & strFntEnd & strTableEnd
Click to expand...
rst.Close
Set rst = Nothing
Click to expand...
'Create e-mail item
Set olApp = Outlook.Application
Set objMail = olApp.CreateItem(olMailItem)
With objMail
'Set body format to HTML
.to = " "
.Subject = "Status of Information Operations submissions to
the NLLS"
' On Error Resume Next
Click to expand...
********** the following line throws the code, everything else returns
expected value sin local
window****************************************** .
Click to expand...
BodyFormat = olFormatHTML
.HTMLBody = "<HTML><BODY>" & strFntNormal & strMessage & " </
BODY></HTML>"
.Display
End With
End Sub
Click to expand...
Thanks
Dave- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

I am working for the military and I tried taking out the line causing
trouble and I can get through the rest of the code in local window and
my email is created with the headers for the table but table is not
populated beyond this. Will check with sending HTML, seems to me we
can not receive HTML formatted emails through NMCI so we might not be
able to send them


Sending a report would be much easier but I have hyperlinks that must
remain and 2003 does not support hyperlinks in reports. Any other
suggestions?
 
D

Dale Fye

Dave,

Actually, when I tried sending the Report as HTML, it worked, since it sent
it as an attachment, rather than as the message formatted as HTML.

Although the Access report may not support hyperlinks (this makes sense
since reports are designed to be printed), when you send it as an HTML
attachment, the hyperlink is retained. When you open the attachments, the
hyperlinks work properly.

HTH
Dale

Dave,

You might also want to check with your Exchange administrator. Based on
the
content of your HTML, I expect you are working for the military (DOTMLPF)
and
I know we are blocked from sending anything other than text (no HTML).

You might also want to consider creating a report, and sending it as HTML
using the SendObject method, rather than automation.

Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.



[email protected] said:
I have modified the following code from another source and get an
error "Automation error- server threw an exception. I have done some
research online but think, make that I know I am way over my head.
Some very specific help would be appreciated.
Click to expand...
I have active hyperlinks that I need to maintain which is why I am
emailing a form rather than sending a report using Access 2003. Other
suggegstions to send this data will be considered
Click to expand...
Private Sub cmdEmailForm_Click()
Dim rst As DAO.Recordset
Dim olApp As Outlook.Application
Dim objMail As Outlook.MailItem
Dim strMessage As String
Click to expand...
Dim strTableBeg As String
Dim strTableEnd As String
Dim strFntNormal As String
Dim strFntHeader As String
Dim strFntEnd As String
Dim strEmailSQL As String
Dim strEmailSelect As String
Dim strEmailFrom As String
Dim strEmailWhere As String
Click to expand...
' build sql statement
strEmailSelect = "Select tblComments.solution, tblBasicData.
[Lesson IDPK], tblNumbered.Numbered_Fleet, tblStatusChoices.Status,
tblComments.Date_Entered, tblBasicData.HyperlinkToLesson,
tblDOTMLPF.DOTMLPF_Choices"
strEmailFrom = "FROM tblDOTMLPF INNER JOIN (qryLastEntry INNER
JOIN (tblStatusChoices INNER JOIN (tblNumbered INNER JOIN
(tblBasicData INNER JOIN tblComments ON tblBasicData.[Lesson IDPK] =
tblComments.Lesson_IDFK) ON tblNumbered.NumberFleetPK =
tblBasicData.NumberedFleetFK) ON tblStatusChoices.StatusChoiceIDPK =
tblComments.statusFK) ON (qryLastEntry.Lesson_IDFK =
tblComments.Lesson_IDFK) AND (qryLastEntry.MaxOfDate_Entered =
tblComments.Date_Entered) AND (qryLastEntry.DOTLMPF_ChoiceFK =
tblComments.DOTLMPF_ChoiceFK)) ON tblDOTMLPF.[DOTMLPF ID PK] =
tblComments.DOTLMPF_ChoiceFK"
strEmailWhere = " Where Numbered_Fleet=""" & Forms![frmPara1]!
[cboNumbered] & """"
strEmailSQL = strEmailSelect & " " & strEmailFrom & " " &
strEmailWhere
Click to expand...
'Define format for output
strTableBeg = "<table border=0>"
strTableEnd = "</table>"
strFntHeader = "<font size=2 face=" & Chr(34) & "Arial" & Chr(34)
& "><b>" & _
"<tr bgcolor=lightblue>" & _
"<td nowrap>Lesson ID</td>" & _
"<td>Hyperlink to Lesson</td>" & _
"<td>DOTMLPF</td>" & _
"<td>Most recent comment</td>" & _
"<td>Status</td>" & _
"<td>Date Entered</td>" & _
"</tr></b></font>"
strFntNormal = "<font color=black face=" & Chr(34) & "Arial" &
Chr(34) & " size=1>"
strFntEnd = "</font>"
Click to expand...
Set rst = CurrentDb.OpenRecordset(strEmailSQL)
Click to expand...
'Build HTML Output for the DataSet
strMessage = strTableBeg & strFntNormal & strFntHeader
'need to test with Outlook
Do Until rst.EOF
strMessage = strMessage & _
"<tr>" & _
"<td>" & rst![LessonID PK] & "</td>" & _
"<td>" & rst!HyperlinkToLesson & "</td>" &
_
"<td>" & rst!DOTMLPF_Choices & "</td>" & _
"<td>" & rst!solution & "</td>" & _
"<td>" & rst!status & "</td>" & _
"<td>" & rst!Date_Entered & "</td>" & _
"</tr>"
rst.MoveNext
Loop
Click to expand...
strMessage = strMessage & strFntEnd & strTableEnd
Click to expand...
rst.Close
Set rst = Nothing
Click to expand...
'Create e-mail item
Set olApp = Outlook.Application
Set objMail = olApp.CreateItem(olMailItem)
With objMail
'Set body format to HTML
.to = " "
.Subject = "Status of Information Operations submissions to
the NLLS"
' On Error Resume Next
Click to expand...
********** the following line throws the code, everything else returns
expected value sin local
window****************************************** .
Click to expand...
BodyFormat = olFormatHTML
.HTMLBody = "<HTML><BODY>" & strFntNormal & strMessage & " </
BODY></HTML>"
.Display
End With
End Sub
Click to expand...
Thanks
Dave- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

I am working for the military and I tried taking out the line causing
trouble and I can get through the rest of the code in local window and
my email is created with the headers for the table but table is not
populated beyond this. Will check with sending HTML, seems to me we
can not receive HTML formatted emails through NMCI so we might not be
able to send them


Sending a report would be much easier but I have hyperlinks that must
remain and 2003 does not support hyperlinks in reports. Any other
suggestions?
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Dynamic query problem 15
Must be over my head / import using transferspreadsheet & dialog * 1
Why there is an error in my Sub in VBA 1

Top