ADAMC
Well-known Member
- Joined
- Mar 20, 2007
- Messages
- 1,169
- Office Version
- 2013
- Platform
- Windows
Hi all
Little stumped by this one so any help appreciated grately.
Basically i have to send out emails to people 30 days from when they attended a training course to provide feedback on the course content.
I have a spreadsheet set up using columns A to E
A is persons name
B is there email address
C is ready to send?
D is Sent?
E is Due date
What happens is this: I have a formula in C which says if the date in column E has passed or is equal to 30 days of the input date show 'Send me today'
I then have a macro (Code below) which looks for this text and sends an email if it sees 'send me today' in column C If it sends a mail it puts the word sent in column D and if i run the macro again it wont send the email where sent appears in column D! (Hope this makes sense)
Anyay my question is this: Can my code be adapted to send the email from a group mail account? At the moment It opens up a mail and sends it from my personal mail account but ideally i need it to email from my group mailbox and not my personal account?
There are 20 people on my team and we each have a personal mailbox but we all have access to a group mailbox we are supposed to use if we send any work related mail.....I hope this makes sense!
Heres the code i have:
Thanks in advance
Little stumped by this one so any help appreciated grately.
Basically i have to send out emails to people 30 days from when they attended a training course to provide feedback on the course content.
I have a spreadsheet set up using columns A to E
A is persons name
B is there email address
C is ready to send?
D is Sent?
E is Due date
What happens is this: I have a formula in C which says if the date in column E has passed or is equal to 30 days of the input date show 'Send me today'
I then have a macro (Code below) which looks for this text and sends an email if it sees 'send me today' in column C If it sends a mail it puts the word sent in column D and if i run the macro again it wont send the email where sent appears in column D! (Hope this makes sense)
Anyay my question is this: Can my code be adapted to send the email from a group mail account? At the moment It opens up a mail and sends it from my personal mail account but ideally i need it to email from my group mailbox and not my personal account?
There are 20 people on my team and we each have a personal mailbox but we all have access to a group mailbox we are supposed to use if we send any work related mail.....I hope this makes sense!
Heres the code i have:
Code:
Sub TestFile_2()
Dim OutApp As Object
Dim OutMail As Object
Dim cell As Range
Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
OutApp.Session.Logon
On Error GoTo cleanup
For Each cell In Sheets("Postbot").Columns("B").Cells.SpecialCells(xlCellTypeConstants)
If cell.Value Like "?*@?*.?*" And LCase(cell.Offset(0, 1).Value) = "send me today" _
And LCase(cell.Offset(0, 2).Value) <> "sent" Then
Set OutMail = OutApp.CreateItem(0)
On Error Resume Next
With OutMail
.To = cell.Value
.Subject = "Post course training assessement"
.Body = "Dear " & cell.Offset(0, -1).Value & vbNewLine & vbNewLine & _
"Test email please fill out the link below blah blah blah"
'You can add files also like this
'.Attachments.Add ("C:\test.txt")
.Send 'Or use Display
End With
On Error GoTo 0
cell.Offset(0, 2).Value = "sent"
Set OutMail = Nothing
End If
Next cell
cleanup:
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub
Thanks in advance