Can i make outlook send mail from a group email account?

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
Hi all :biggrin:

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 :)
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
Maybe i made my post more complicated than it needed to be!

Basically i want to adapt the code above so it always send an email from:

So i want to add a from field to the code above so when it opens outlook a from field will be there with the email address 'adamc@test.com'

Thanks!
 

ADAMC

Well-known Member
Joined
Mar 20, 2007
Messages
1,169
Office Version
  1. 2013
Platform
  1. Windows
Incase anyone found the code above useful i found the answer to my problem:

This line of code:

.SentOnBehalfOfName = """group mailbox name"" <group mailbox email address>"

allows you to send a mail on behalf of another person or mailbox

Thanks
 

noppojp

Board Regular
Joined
Nov 4, 2005
Messages
69
Changing the above code to run on Save

Any way to make this code work NOT through manual invocation but when the user goes to save the workbook?

My goal is to be informed when a user changes the workbook, and NOT to receive the workbook.

FWIW, i have taken the code above, played with it and got it to send hardcoded path and the file name which results in a link that will take me to the file - benefits are faster send, 841B vs 545KB. This is exactly what's needed for me, BUT the code needs to run immediately prior to the user saving the file.

Any idea how to modify the above to enable this?

Thanks!!
 

noppojp

Board Regular
Joined
Nov 4, 2005
Messages
69
Resolved: Send email when user goes to save the wkbk

i'm having a conversation with myself here.
The code below enabled me to have Outlook inform me when someone updates a file. The file name tells me who updated the file, as there are dedicated files for users. Each users file contains the code below.
The users opens the workbook, does whatever, and when they go to save as or save, i'm notified. Helps me to keep the bandwidth down, speeds sending the email, etc. Works fine for me.
Code:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim OutApp As Object
    Dim OutMail As Object
    Dim cell As Range
    Dim Who As String
      
    Application.ScreenUpdating = True
    Set OutApp = CreateObject("Outlook.Application")
    OutApp.Session.Logon
    Filename = ActiveWorkbook.Name
    Who = "first.last@company.com"
      
	Set OutMail = OutApp.CreateItem(0)
        With OutMail
                .To = Who
                .Subject = "*** ACTION REQUIRED *** SM Updated a request form!"
                .Body = "*** THIS NOTE AUTOGENERATED - ACTION REQUIRED ***" & vbNewLine & vbNewLine & _
                        "The following was updated: \\JPNNCGDF01\root_shr01\IT\BTO_Vendor_Mgmt\EXP_APP_FORM\" & Filename & vbNewLine & vbNewLine & _
                        "  "
                'You can add files also like this
                '.Attachments.Add ("C:\test.txt")
                .Send  'Or use Display
            End With
            On Error GoTo 0

       Set OutMail = Nothing
cleanup:
    Set OutApp = Nothing
    Application.ScreenUpdating = True
    MsgBox "XXX XX has been notified of your changes. Thank you!"
End Sub
[code][/quote]
 

Forum statistics

Threads
1,181,102
Messages
5,928,073
Members
436,587
Latest member
Slicesofquince

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top