VBA Help - Email using different email address

neilbryan

New Member
Joined
May 20, 2019
Messages
2
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
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,661
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
 

neilbryan

New Member
Joined
May 20, 2019
Messages
2
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
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:
[COLOR=#333333]Sub Mail_Every_Worksheet_With_Address_In_B2_PDF()[/COLOR]
[COLOR=#333333]'Working only in 2007 and up[/COLOR]
[COLOR=#333333]Dim sh As Worksheet[/COLOR]
[COLOR=#333333]Dim TempFilePath As String[/COLOR]
[COLOR=#333333]Dim TempFileName As String[/COLOR]
[COLOR=#333333]Dim FileName As String[/COLOR]


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


[COLOR=#333333]'Loop through every worksheet[/COLOR]
[COLOR=#333333]For Each sh In ThisWorkbook.Worksheets[/COLOR]
[COLOR=#333333]FileName = ""[/COLOR]


[COLOR=#333333]'Test B2 for a mail address[/COLOR]
[COLOR=#333333]If sh.Range("B2").Value Like "?*@?*.?*" Then[/COLOR]



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


[COLOR=#333333]FileName = RDB_Create_PDF(Source:=sh, _[/COLOR]
[COLOR=#333333]FixedFilePathName:=TempFileName, _[/COLOR]
[COLOR=#333333]OverwriteIfFileExist:=True, _[/COLOR]
[COLOR=#333333]OpenPDFAfterPublish:=False)[/COLOR]


[COLOR=#333333]'If publishing is OK create the mail[/COLOR]
[COLOR=#333333]If FileName <> "" Then[/COLOR]
[COLOR=#333333]Dim OutApp As Outlook.Application[/COLOR]
[COLOR=#333333]Dim OutMail As Outlook.MailItem[/COLOR]
[COLOR=#333333]Dim strbody As String[/COLOR]


[COLOR=#333333]Set OutApp = CreateObject("Outlook.Application")[/COLOR]
[COLOR=#333333]Set OutMail = OutApp.CreateItem(olMailItem)[/COLOR]

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



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


[COLOR=#333333]End If[/COLOR]
[COLOR=#333333]Next sh[/COLOR]
[COLOR=#333333]End Sub [/COLOR]
 

sijpie

Well-known Member
Joined
Nov 1, 2008
Messages
3,661
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,102,624
Messages
5,487,940
Members
407,616
Latest member
MichaelaL

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top