UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> Ms Word Document As The Body Of An Email Created And Sent From Ms Access, Access 2016    
 
   
magicscreen
post Dec 2 2017, 04:11 PM
Post#21



Posts: 450
Joined: 18-November 07



Hi JonSmith,

I can use your code if I type in the email address of the person I am sending the email to.

But when using code to enter the email address I get the following message and the email stays in the Sent folder.


Task 'ArtSterling - Sending' reported error (0x800CCC0F) : 'The connection to the server was interrupted. If this problem continues, contact your server administrator or Internet service provider (ISP). The server responded: 550 5.1.0 ESMTP server not available AUP#O-1070'

CODE
Dim strBody As String
Dim strMessage As String

'With email with add specific stuff
            With olEmail.objEmail
                'Add the attachment
                .Attachments.Add strAttachmentPath
        
                
                If rsCurr!Delete_YN = False Then
                    
                
                    strBody = .HTMLBody
  
I AM NOT SURE IF I NEED THE NEXT TWO LINES BUT I COMMENTED THEM OUT
          
            '         strBody = Replace(strBody, "#DATE#", shtRibbon.Range("ribProcessDate"))
            '        .Subject = Replace(.Subject, "#DATE#", shtRibbon.Range("ribProcessDate"))
                     .Subject = "Fight Rising Healthcare Costs for your Employees"
            
                    'Display the email and fix the hmtl body
                    '.Display
                    .HTMLBody = strBody
                    .To = Trim(rsCurr!EmpEmail)
                    strMessage = .To
                    .Display
                    .Send
                  
                End If
Go to the top of the page
 
magicscreen
post Dec 2 2017, 04:39 PM
Post#22



Posts: 450
Joined: 18-November 07



Hi JonSmith,

If I type in the email address of the recipient it seems to work but if I enter the email address via code it sends the email to the Outlook Sent folder and I get an error message:

Task 'ArtSterling - Sending' reported error (0x800CCC0F) : 'The connection to the server was interrupted. If this problem continues, contact your server administrator or Internet service provider (ISP). The server responded: 550 5.1.0 ESMTP server not available AUP#O-1070'

I called my ISP and spoke to a Microsft tech and neither one knows what is causing this error.

This is some of my code:

CODE
Dim strBody As String
Dim strMessage As String
    
    Set dbCurr = CurrentDb
    Set rsCurr = dbCurr.OpenRecordset("Select * From tblEmailRecipients Where Recipient_YN = True Order By EmpFullName", dbOpenDynaset, dbSeeChanges)

    rsCurr.MoveLast
    rsCurr.MoveFirst

    While Not rsCurr.EOF
    
        If Nz(rsCurr!EmpEmail, "") <> "" Then
    
            'Create a new instance of the email class and set the template path and mail mode
            Set olEmail = New clsMailItemEvents
            olEmail.TemplatePath = Application.CurrentProject.Path & "\AmeriplanEmail.oft"
            olEmail.MailMode = 1 '1 For sending, 0 for edit template
        
            'With email with add specific stuff

            With olEmail.objEmail
                'Add the attachment
                .Attachments.Add strAttachmentPath
        
                'We save the html body here because when we .display the email the users default signature will be added.
                'We want to overule this so we replace the entire email body with this cached version which doesn't contain a sig.
                
                If rsCurr!Delete_YN = False Then
                    
                
                    strBody = .HTMLBody
            
I AM NOT SURE WHAT THE NEXT LINE IS FOR
                    'Replace the #NAME# part, used if you want any dynamic text in your body or subject

I AM NOT SURE IF I NEED THE FOLLOWING TWO LINES BUT I COMMENTED THEM OUT

            '        strBody = Replace(strBody, "#DATE#", shtRibbon.Range("ribProcessDate"))
            '        .Subject = Replace(.Subject, "#DATE#", shtRibbon.Range("ribProcessDate"))
                  
                     .Subject = "Fight Rising Healthcare Costs for your Employees"
            
                    'Display the email and fix the hmtl body
                    '.Display
                    .HTMLBody = strBody
                    .To = Trim(rsCurr!EmpEmail)
                    strMessage = .To
                    .Display
                    .Send

                 End If
                
            End With
       rsCurr.Movenext
   Wend
Go to the top of the page
 
magicscreen
post Dec 23 2017, 02:17 PM
Post#23



Posts: 450
Joined: 18-November 07



Hi JonSmith,

Sorry to bother you again.

The email code you sent me works like a charm.

However, I have one last, I hope, item.

I have two email accounts in my outlook and I want to use a specific account to send my emails.

I tried setting up SendUsingAccount but am having trouble with it.

I wanted to set up something like dim oAccount as Outlook.Account

Then I need to set it to the correct account but when I tried to use Application.Session.Accounts I get a message stating that Session is readonly.

If I can set up oAccount to point to the correct Outlook account then I believe I can use SendUsingAccount.


This is what I am using

With olEmail.objEmail
'Add the attachment
'.Attachments.Add strAttachmentPath

'We save the html body here because when we .display the email the users default signature will be added.
'We want to overule this so we replace the entire email body with this cached version which doesn't contain a sig.

If rsCurr!Delete_YN = False Then


strBody = .HTMLBody

.Subject = "Fight Rising Healthcare Costs for your Employees"

'Display the email and fix the hmtl body
'.Display
.HTMLBody = strBody
.To = Trim(rsCurr!EmpEmail)
strMessage = .To

.SendUsingAccount


'.Display
.Send I WOULD LIKE TO USE SENDUSINGACCOUNT instead of SEND if possible.

End If

End With

Can you help?

Thanks,

magicscreen
This post has been edited by magicscreen: Dec 23 2017, 02:27 PM
Go to the top of the page
 
DanielPineault
post Dec 23 2017, 03:07 PM
Post#24


UtterAccess VIP
Posts: 5,914
Joined: 30-June 11



You don't replace .Send with .SendUsingAccount. Take a look at https://www.pcreview.co.UK/threads/VBA-to-s...ccount.3997983/ for a concrete example on how to implement it. It is a question of iterating through all the accounts until you find the one you wish to use and then assign it to a variable to use in your e-mail code. The example should clear it all up.

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
JonSmith
post Dec 24 2017, 09:29 AM
Post#25



Posts: 3,300
Joined: 19-October 10



Sorry I missed all those posts.

So, the code you indicated you weren't sure if you needed. That is leftover from me using it somewhere, sorry about that.
The idea was basically if I wrote #DATE# anywhere in the email template the code code replace that with the relevant date value, same for #NAME# or any kind of value I wanted to be dynamically added with VBA code.

As for your issue with the Send to, dunno, but sounds like you solved it and looks like Daniel has some links for the send using account.

The method I demonstrated is only to do with using a template file rather than writing html in VBA, the rest of it works as normal VBA automation so looking up stuff like Daniels link should give you the info you need.
Go to the top of the page
 
magicscreen
post Dec 24 2017, 01:35 PM
Post#26



Posts: 450
Joined: 18-November 07



Daniel and Jon,

Thanks for your responses. I have most of the situation in hand.

Daniel helped me with the .SendUsingAccount and it is working.

I can now send group emails from a specific Outlook Account.

I get some emails not delivered, usually because they are no longer being used.

I now using VBA scan the Outlook Inbox for these emails and place the email addresses into a table.

I then delete the emails from the table and set a flag in my main database table to reflect that that entry is now closed.

The operation takes a long time so I set up a slider to give the user an idea of approximately how mush time it will take.

This produces the following issue. It takes a long time for this process to complete and the slider reaches the end around 2 minutes or more before the process actually completes.

I attached my slider settings. I tried different combinations with no luck.

Thanks

Attached File(s)
Attached File  SliderImage.jpg ( 20.75K )Number of downloads: 0
 
Go to the top of the page
 
magicscreen
post Dec 25 2017, 11:17 AM
Post#27



Posts: 450
Joined: 18-November 07



I am having an issue with a query.

I want to insert a record into a table based on fields from the query below which works because I use it elsewhere named qryFollowUp

SELECT tblCompany.ID, tblTrackSales.ID, tblTrackSales.DateContacted, tblTrackSales.Company, tblCompany.Timezone, tblTrackSales.Department, tblTrackSales.Action, tblTrackSales.FollowUpDate, tblCompany.Contact, tblCompany.Phone, tblCompany.Ext, tblCompany.Fax, tblCompany.Email, tblCompany.AltContact, tblCompany.AltPhone, tblCompany.AltExt, tblCompany.AltFax, tblCompany.AltEmail, tblCompany.Closed_YN, tblCompany.NumbEmp, tblTrackSales.WaitingForResponse, tblTrackSales.ID AS FollowUpID, tblCompany.State
FROM tblTrackSales INNER JOIN tblCompany ON tblTrackSales.ID = tblCompany.ID
ORDER BY tblTrackSales.FollowUpDate, tblCompany.Company;

The fields going into the table are: EmpFullName, EmpEmail, Company, FollowUpID from the results of the above query.

The criteria are: Instr(Action, 'Wrong') = 0 And WaitingForResponse = False And Closed_YN = False And Trim(Action) = Trim(CStr(gRecipientFollowupDate)
I am tried to write an Insert query similar to the query below. I know the query below is incorrect.

gRecipientFollowupDate is a Public date variable populated from the form.

[code]

strSQL = "Insert INTO tblEmailRecipients (EmpFullName, EmpEmail, Company, FollowUpID) Select Contact, EMail, Company, FollowUpID From qryFollowUp Where Exists
(Select * From qryFollowUp f2 Where Instr(f2.Action, 'Wrong') = 0 And f2.WaitingForResponse = False And f2.Closed_YN = False And Trim(f2.Action) = '" & Trim(CStr(gRecipientFollowupDate)

/code]


Go to the top of the page
 
2 Pages V < 1 2


Custom Search
RSSSearch   Top   Lo-Fi    25th May 2018 - 10:58 PM