Excel VBA send on behalf of not working everytime

[claus1975]

Hi,

I got this code for sending a lot of mails on behalf of a shared mailbox in Outlook (Office365)
It's the same code that runs for each mail but at some point it start sending mails from myself and not on behalf of the shared mailbox. Sometimes it send 100 mails before it happens other times it could be 150 times. Does anybody have a clue what is going on?

Sub MAtekst(tekst1 As String, tekst2 As String, Mail As String, disp As String, header As String)    Dim OutApp As Object, OutMail As Object
    Dim wsSend As Worksheet, wsTemp As Worksheet
    Dim StrBody1 As String, StrBody2 As String
    Dim rowstemp As Integer
    Dim rng As Range
    
    Set wsSend = ThisWorkbook.Worksheets("Send")
    Set wsTemp = Worksheets("Temp")
    rowstemp = wsTemp.Cells(wsTemp.Rows.Count, "A").End(xlUp).row
    StrBody1 = "****** style='font-family:calibri;font-size:11pt'>" & tekst1
    StrBody2 = "****** style='font-family:calibri;font-size:11pt'>" & tekst2
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    Set rng = wsTemp.Range("A1:D" & rowstemp).SpecialCells(xlCellTypeVisible)
        On Error Resume Next
            With OutMail
            .To = Mail
            .Subject = header
            .GetInspector.Activate
            .HTMLBody = StrBody1 & RangetoHTML(rng) & StrBody2
            .SentOnBehalfOfName = wsSend.Cells(1, 2)
            .Send
            End With
        End If
        On Error GoTo 0
End Select

Hoping for some help from you experts

Best Regards
Claus

 

[Coding4Fun]

What is in wsSend.Cells(1, 2)

Silly question but do you have permission to send for each person you are trying to send as?

Also noticed End Select? at the end of your code? For?

 

[paulxmw]

you could try

if its always the same email address

.SentOnBehalfOfName = "email.address@here.com"
.To = Cws.Cells(Rnum, 1).Value
.Mail = Item.Forward = "paul.clubley@leeds.gov.uk"
.Subject = "Action requires" & " " & ActiveWorkbook.Name
.HTMLBody = StrBody & RangetoHTML(rng) & sBody 'changed
.Display 'Or use Send

Or if you use different email addresses

.SentOnBehalfOfName = Sheets("Sheet1").Range("A").Value ' where the email address is set on a sheet
.To = Cws.Cells(Rnum, 1).Value
.Mail = Item.Forward = "paul.clubley@leeds.gov.uk"
.Subject = "Action requires" & " " & ActiveWorkbook.Name
.HTMLBody = StrBody & RangetoHTML(rng) & sBody 'changed
.Display 'Or use Send

Paul xmw

 

[claus1975]

Hi,
wsSend.Calls(1,2) is the send of behalf of mail-address.
Yes I do have permission to send from the shared mailbox. I can see Outlook in another screen and when I can see that it start sending from my own adress I break the code and resume it again and then there is no problem sending on behalf of the next 50-150 mails.

 

[claus1975]

you could try

if its always the same email address

.SentOnBehalfOfName = "email.address@here.com"
.To = Cws.Cells(Rnum, 1).Value
.Mail = Item.Forward = "paul.clubley@leeds.gov.uk"
.Subject = "Action requires" & " " & ActiveWorkbook.Name
.HTMLBody = StrBody & RangetoHTML(rng) & sBody 'changed
.Display 'Or use Send

Or if you use different email addresses

.SentOnBehalfOfName = Sheets("Sheet1").Range("A").Value ' where the email address is set on a sheet
.To = Cws.Cells(Rnum, 1).Value
.Mail = Item.Forward = "paul.clubley@leeds.gov.uk"
.Subject = "Action requires" & " " & ActiveWorkbook.Name
.HTMLBody = StrBody & RangetoHTML(rng) & sBody 'changed
.Display 'Or use Send

Paul xmw

The send from behalf of is always the same address but I'm sending to many different mailaddresses (500+)
I did try to put in a code to stop if the send of behalf of address wasn't right but it is just not when it's sending the mail in Outlook

 

[claus1975]

Also noticed End Select? at the end of your code? For?

End Select is just for some other code choosing if I want to have the mail displayed before sending them (Case select)
It's not used as I always just send them without diplaying.

 

[claus1975]

you could try

if its always the same email address

.SentOnBehalfOfName = "email.address@here.com"
.To = Cws.Cells(Rnum, 1).Value
.Mail = Item.Forward = "paul.clubley@leeds.gov.uk"
.Subject = "Action requires" & " " & ActiveWorkbook.Name
.HTMLBody = StrBody & RangetoHTML(rng) & sBody 'changed
.Display 'Or use Send

Or if you use different email addresses

.SentOnBehalfOfName = Sheets("Sheet1").Range("A").Value ' where the email address is set on a sheet
.To = Cws.Cells(Rnum, 1).Value
.Mail = Item.Forward = "paul.clubley@leeds.gov.uk"
.Subject = "Action requires" & " " & ActiveWorkbook.Name
.HTMLBody = StrBody & RangetoHTML(rng) & sBody 'changed
.Display 'Or use Send

Paul xmw

Thanks. Somehow it works everytime when entering the mailaddress in the macro instead of taking it from the cell

 

[Coding4Fun]

Thanks. Somehow it works everytime when entering the mailaddress in the macro instead of taking it from the cell

Range A+What Row?

[COLOR=#333333][I].SentOnBehalfOfName = Sheets("Sheet1").[/I][/COLOR][COLOR=#ff0000][I]Range("A")[/I][/COLOR][COLOR=#333333][I].Value ' where the email address is set on a sheet[/I][/COLOR]

 

[claus1975]

Range A+What Row?

[COLOR=#333333][I].SentOnBehalfOfName = Sheets("Sheet1").[/I][/COLOR][COLOR=#ff0000][I]Range("A")[/I][/COLOR][COLOR=#333333][I].Value ' where the email address is set on a sheet[/I][/COLOR]

I didn't use that one.
I changed ".SentOnBehalfOfName = wsSend.Cells(1, 2)" to ".SentOnBehalfOfName = myemail@something.com"

Br,
Claus