Send email from access vba using outlook V2 (1 Viewer)

[Bar_NZ]

Hi All, can I please ask where I am supposed to put the MailItem.SentOnBehalfOfName in the code that is mentioned in the thread of the same name without the V2, lol

Any help would be greatly appreciated.

 

[pbaldy]

Anywhere in the With block.

I'll be visiting NZ next month; hope the weather holds!

 

[Bar_NZ]

Depends on where your visiting, lol, nah, the weather is usually pretty good till the end April mid May....

 

[pbaldy]

I think we're visiting practically everywhere! Flying into Auckland, taking the train to Wellington (daughter lives there), flying to the South Island with her for a side trip, visiting Queenstown, Christchurch and points in between. On a previous visit we stayed close to Wellington, so looking forward to seeing more of the country this time.

 

[Bar_NZ]

Sounds like a great trip, I really hope you enjoy yourself, the SI is awesome and I reckon the weather will be good for you....

 

[Bar_NZ]

Here is my code, It's a bit messy but I still get a "Object Required" 424 at the "With", I can't see where I'm going wrong, because I'm new to this, can you help me please??

Private Sub Command23_Click()
Dim db As DAO.Database
Dim MailList As DAO.Recordset
Dim MyOutlook As Outlook.Application
Dim MyMail As Outlook.MailItem
Dim Subjectline As String
Dim BodyFile As String
Dim MyBodyText As String
Dim rsemail As DAO.Recordset
Dim ns As Outlook.NameSpace
Dim Folder As Outlook.MAPIFolder
Dim mysql As String
Subjectline$ = "News Letter"
DoCmd.SetWarnings False
Set MyOutlook = New Outlook.Application
Set MyOutlook = CreateObject("Outlook.Application")
Set ns = MyOutlook.GetNamespace("MAPI")
Set Folder = ns.GetDefaultFolder(olFolderInbox)
MyOutlook.Explorers.Add Folder
Set rsemail = CurrentDb.OpenRecordset("eMail")
Set MyMail = MyOutlook.CreateItem(olMailItem)
With MarkAsTask = MailItem.SentOnBehalfOfName("Info@somewhere.co.nz")
End With
Do Until rsemail.EOF

MyMail.BCC = MyMail.BCC & rsemail!eMail & ";"

'And on to the next one...
rsemail.MoveNext

Loop
'This gives it a subject
MyMail.Subject = Subjectline$
MyMail.Body = "Hello," & Chr(13) & Chr(13) & "Please find attached your copy of the current news letter." & Chr(13) & Chr(13) & "Thank You from the Team"
MyMail.Display
Set MyMail = Nothing
Set MyOutlook = Nothing
DoCmd.SetWarnings True
rsemail.Close
db.Close
Set db = Nothing

End Sub

 

[pbaldy]

Try replacing the bold with:

MyMail.SentOnBehalfOfName("Info@somewhere.co.nz")

 

[Bar_NZ]

:banghead:, Lol...

Compile Error:

Wrong number of arguments or invalid property assignment


With MyMail.SentOnBehalfOfName("Info@nzrwelfare.co.nz")
End With

 

[pbaldy]

Just the line I posted, no "With" and no "End With".

 

[Bar_NZ]

invalid use of property

 

[pbaldy]

Duh. :banghead:

MyMail.SentOnBehalfOfName = "Info@nzrwelfare.co.nz"

 

[Bar_NZ]

Well that worked, now I'm being a total dumbass....

Runtime error 3265: Item not found in collection for the following reference

MyMail.BCC = MyMail.BCC & rsemail!eMail & ";"

By the way, I really do appreciate your assistance here...

 

[pbaldy]

No problem, just make sure you keep the weather nice!

My first thought is that "email" is not a field in the table or query you opened the recordset on. It had the same name, so in this spot you want the name of the field, not the name of the table/query.

 

[Bar_NZ]

OMG, I am a total dumbass, That has total fixed it and it is now working perfectly, you absolutely ROCK. Problem solved.

I will ensure the weather is amazing for you!!!

 

[pbaldy]

Awesome! Glad we got it working, and can't wait to visit your beautiful country.

 

[Bar_NZ]

I'm sure your daughter is saying this, the weather in Wellington at the moment is awesome, and the rest of the country is pretty much the same, I would recommend taking the rail trip from Christchurch to Greymouth, it's spectacular, that's if you have time or maybe on your next trip.

Once again, I thank you for your help and appreciate every word of it.

 

[pbaldy]

Thanks! I appreciate the idea, and I'll mention it to her. I know she's putting things together for our Queenstown/Christchurch trip, so I'll see if that can be fit in. Looking forward to the train trip from Auckland to Wellington, which we've heard has some nice scenery.

 

[Bar_NZ]

Oh it is, I work for KiwiRail and that is one of the good ones, so is the Costal Pacific that travels the coast of the South Island, however know matter what you do, it will be memorable.

Thanks again and have a great trip.