Results 1 to 4 of 4

Thread: VBA Help - Email using different email address
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Help - Email using different email address

    Hi All. I am currently trying to use a macro to pdf each individual sheet to email addresses in B2 of each sheet. This works perfectly. However I want to email using a different email account to my default one. This doesn't work perfectly! I have two seperate VBA scripts, one for PDF and send that works, one to email from a different email account, and that works. However I cannot combine them. Can anybody help? Here is my code for the PDF and email each seperate sheet:


    Sub Mail_Every_Worksheet_With_Address_In_B2_PDF()
    'Working only in 2007 and up
    Dim sh As Worksheet
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileName As String


    'Temporary path to save the PDF files
    'You can also use another folder like
    'TempFilePath = "C:\Users\Ron\MyFolder"
    TempFilePath = Environ$("temp") & ""


    'Loop through every worksheet
    For Each sh In ThisWorkbook.Worksheets
    FileName = ""


    'Test B2 for a mail address
    If sh.Range("B2").Value Like "?*@?*.?*" Then



    'If there is a mail address in B2 create the file name and the PDF
    TempFileName = TempFilePath & sh.Name & " " _
    & Format(Now, "dd-mmm-yy") & ".pdf"


    FileName = RDB_Create_PDF(Source:=sh, _
    FixedFilePathName:=TempFileName, _
    OverwriteIfFileExist:=True, _
    OpenPDFAfterPublish:=False)


    'If publishing is OK create the mail
    If FileName <> "" Then
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim strbody As String


    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)

    RDB_Mail_PDF_Outlook FileNamePDF:=FileName, _
    StrTo:=sh.Range("B2").Value, _
    StrCC:="", _
    StrBCC:="", _
    StrSubject:="Weekly Certificate - Millisun", _
    Signature:=True, _
    Send:=False, _
    strbody:="Good morning<br><br>" & _
    "******>Please find your weekly certificate." & _
    "<br><br>" & "Regards Millisun Accounts</body>"



    Else
    MsgBox "Not possible to create the PDF, possible reasons:" & vbNewLine & _
    "Microsoft Add-in is not installed" & vbNewLine & _
    "You Canceled the GetSaveAsFilename dialog" & vbNewLine & _
    "The path to Save the file in arg 2 is not correct" & vbNewLine & _
    "You didn't want to overwrite the existing PDF if it exist"
    End If


    End If
    Next sh
    End Sub

  2. #2
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,463
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Help - EMail using different email address

    Where is your code to send from a different email?

    When you post code, please use code tags (see below how to do that). A lot of regulars here won't even answer your post if you don't use them
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

  3. #3
    New Member
    Join Date
    May 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Help - EMail using different email address

    Quote Originally Posted by sijpie View Post
    Where is your code to send from a different email?

    When you post code, please use code tags (see below how to do that). A lot of regulars here won't even answer your post if you don't use them
    Thankyou, I did not realise. Just to reiterate I have the below code from RDB which works really well. However I want it to email from a secondary Outlook account and not the default one. Can anyone help please?


    Code:
    Sub Mail_Every_Worksheet_With_Address_In_B2_PDF()
    'Working only in 2007 and up
    Dim sh As Worksheet
    Dim TempFilePath As String
    Dim TempFileName As String
    Dim FileName As String
    
    
    'Temporary path to save the PDF files
    'You can also use another folder like
    'TempFilePath = "C:\Users\Ron\MyFolder"
    TempFilePath = Environ$("temp") & ""
    
    
    'Loop through every worksheet
    For Each sh In ThisWorkbook.Worksheets
    FileName = ""
    
    
    'Test B2 for a mail address
    If sh.Range("B2").Value Like "?*@?*.?*" Then
    
    
    
    'If there is a mail address in B2 create the file name and the PDF
    TempFileName = TempFilePath & sh.Name & " " _
    & Format(Now, "dd-mmm-yy") & ".pdf"
    
    
    FileName = RDB_Create_PDF(Source:=sh, _
    FixedFilePathName:=TempFileName, _
    OverwriteIfFileExist:=True, _
    OpenPDFAfterPublish:=False)
    
    
    'If publishing is OK create the mail
    If FileName <> "" Then
    Dim OutApp As Outlook.Application
    Dim OutMail As Outlook.MailItem
    Dim strbody As String
    
    
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(olMailItem)
    
    RDB_Mail_PDF_Outlook FileNamePDF:=FileName, _
    StrTo:=sh.Range("B2").Value, _
    StrCC:="", _
    StrBCC:="", _
    StrSubject:="Weekly Certificate", _
    Signature:=True, _
    Send:=False, _
    strbody:="Good morning<br><br>" & _
    "******>Please find your weekly certificate." & _
    "<br><br>" & "Regards Accounts</body>"
    
    
    
    Else
    MsgBox "Not possible to create the PDF, possible reasons:" & vbNewLine & _
    "Microsoft Add-in is not installed" & vbNewLine & _
    "You Canceled the GetSaveAsFilename dialog" & vbNewLine & _
    "The path to Save the file in arg 2 is not correct" & vbNewLine & _
    "You didn't want to overwrite the existing PDF if it exist"
    End If
    
    
    End If
    Next sh
    End Sub 

  4. #4
    Board Regular
    Join Date
    Nov 2008
    Location
    Netherlands
    Posts
    3,463
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: VBA Help - EMail using different email address

    OK. What you psted is the code to send pdf from your current email address. That one works you say.
    What is the code you use to send from a different address?
    Short Guide to Better VBA - Link: https://www.mrexcel.com/forum/showthread.php?t=712119

    Please use code tags around your code:
    [Code] Your code here... [/Code]




    Engelse lessen, persoonlijk en doelgericht. Dutch tuition tailor-made for you. https://Ennef.nl

    Wearable for people with panic attacks: sidjup https://sidjup.com

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •