VBA Help - Email using different email address

[neilbryan]

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

 

[sijpie]

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]

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?

[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]

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?