Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

VBA code to send an automated email from a specific outlook email address(when I have 2 mailboxes)

0

Guys,

PFB the Code I use to send out automated reports to my team. The code works flawlessly. But, I need help with the below.

I am currently sending the email from my official account(email1@company.com). I shared mailbox address in my my outlook(email2@company.com). While I want email1 to be my primary email account I want this report alone to go from email2. I know I can do it manually but that would not be efficient. Do help in this. 

Sub adscallout()

'set range
Dim r As Range
Set r = Sheets("pivot").Cells

'loop
For i = 2 To 100
    Range("B4").Select
    ActiveSheet.PivotTables("PivotTable2").PivotFields("user_login").ClearAllFilters
    ActiveSheet.PivotTables("PivotTable2").PivotFields("user_login").CurrentPage = _
        Sheet3.Range("A" & i).Value
    ActiveWorkbook.EnvelopeVisible = True
 
    With r.Parent.MailEnvelope.Item
    .To = Sheet3.Range("B" & i).Value
    '.cc = Sheet3.Range("E" & i).Value
    .Subject = "REALTIME DA Timeline Metrics : 02-Jul "
    '.display
    .send
    End With
    
Next i
   

End Sub
excelmacros-vba
MS Office Version: Version 16
posted by: KrishKathir18 (rep: 2) on Fri Jul 2, 2021 at 1:28 am
Answer
Discuss

Answers

0
Selected Answer

Krish

If you're prepared to use Outlook application to send email (which I prefer to Excel's .Mailenvelope), please follow Ron de Bruin's article Use the mail account you want in your mail macro and modify your macro to suit.

posted by: John_Ru (rep: 6142) on Fri Jul 2, 2021 at 8:25 am
Discuss

Discussion

John,

Tried the above code. I am able to get the email sent. But, it isn't selecting the data in the pivot(after filtering the user_login). The body of the email is empty. Could you help ?

PFB the Code: 


Sub adscallout()


    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim OutAccount As Outlook.Account
    Dim r As Range
    Dim strbody As String
    Set r = Sheets("pivot").Cells
    

    'loop
 For I = 2 To 100
    Range("B2").Select
    
ActiveSheet.PivotTables("PivotTable2").PivotFields("user_login").ClearAllFilters

ActiveSheet.PivotTables("PivotTable2").PivotFields("user_login").CurrentPage = _
Sheet3.Range("A" & I).Value


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
  
    
    'Use the first account, see that Item is 1 now
    Set OutAccount = OutApp.Session.Accounts.Item(1)
    
    'Or us the name instead of the number
    'Set OutAccount = OutApp.Session.Accounts("ron@something.nl")
    
    With OutMail
        .To = ""
        .CC = ""
        .BCC = ""
       .SendUsingAccount = OutAccount
        .Send
        
    End With
    
Next I
   

End Sub
KrishKathir18 (rep: 2) Jul 2, '21 at 12:44 pm
Krish. Don't have any time left today, sorry. Suggest you look on that site for emailling a range as html (as I said, I was leaving you to modify the macro).
John_Ru (rep: 6142) Jul 2, '21 at 1:01 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login