send email throw excel

[malek_ali7sas]

dear Gents ,


I have a query if their is any one can help me ,


i have an excel file separated to sheets depend on the manager name each sheet contain information for employees these employees , in the informations i have the email for the manager


my question how i can send email to the manager these data

thank you

 

[FormR]

Hi, the code in this link might help you get started.

https://www.rondebruin.nl/win/s1/outlook/amail5.htm

 

[malek_ali7sas]

Hi, the code in this link might help you get started.

https://www.rondebruin.nl/win/s1/outlook/amail5.htm

Thanks very much

 

[malek_ali7sas]

i have one question ?

if i have more than signature how i can choose it ?

if i want to keep the user to inter the body message is it possible ؟

the code below please any one can help me for my query
---------------------------------------------------------------------------------------------------
Sub Mail_Every_Worksheet()
Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
TempFilePath = Environ$("temp") & ""


'You use Excel 2007-2016
FileExtStr = ".xls": FileFormatNum = 52


With Application
.ScreenUpdating = False
.EnableEvents = False
End With


Set OutApp = CreateObject("Outlook.Application")


For Each sh In ThisWorkbook.Worksheets

If sh.Range("A2").Value Like "?*@?*.?*" Then


sh.Copy
Set wb = ActiveWorkbook


TempFileName = sh.Name


Set OutMail = OutApp.CreateItem(0)



With wb

.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum

On Error Resume Next

With OutMail

.Attachments.Add wb.FullName

.Display

strbody = "HI sony "



.to = sh.Range("A2").Value
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = "HI sony " & "<br>" & .HTMLBody
.Send



'You can add other files also like this
'.Attachments.Add ("C:\test.txt")


End With
On Error GoTo 0


.Close savechanges:=False
End With

Set OutMail = Nothing


Kill TempFilePath & TempFileName & FileExtStr


End If
Next sh


Set OutApp = Nothing


With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

 

[malek_ali7sas]

Nobody can help me regarding the above issue ?

 

[FormR]

if i have more than signature how i can choose it ?

See if this link helps..

https://www.rondebruin.nl/win/s1/outlook/signature.htm

if i want to keep the user to inter the body message is it possible ؟

I'm not sure what you are asking for here?

 

[malek_ali7sas]

My dear i try to merge that codes nothing change the signature not appear

------------------------------------------------------------------------------------------
Sub Mail_Every_Worksheet() Dim sh As Worksheet
Dim wb As Workbook
Dim FileExtStr As String
Dim FileFormatNum As Long
Dim TempFilePath As String
Dim TempFileName As String
Dim OutApp As Object
Dim OutMail As Object
Dim strbody As String
Dim SigString As String
Dim Signature As String
TempFilePath = Environ$("temp") & ""


'You use Excel 2007-2016
FileExtStr = ".xls": FileFormatNum = 52


With Application
.ScreenUpdating = False
.EnableEvents = False
End With


Set OutApp = CreateObject("Outlook.Application")


For Each sh In ThisWorkbook.Worksheets

If sh.Range("A2").Value Like "?*@?*.?*" Then


sh.Copy
Set wb = ActiveWorkbook


TempFileName = sh.Name


Set OutMail = OutApp.CreateItem(0)



With wb

.SaveAs TempFilePath & TempFileName & FileExtStr, FileFormat:=FileFormatNum

On Error Resume Next

With OutMail

.Attachments.Add wb.FullName
strbody = "HI "

SigString = Environ("appdata") & "\Microsoft\Signatures\TEST.htm"

.Display




If Dir(SigString) <> "" Then
Signature = .GetBoiler(SigString)
Else
Signature = ""
End If


On Error Resume Next



.to = sh.Range("A2").Value
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.Body = strbody & vbNewLine & Signature




'.Send



'You can add other files also like this
'.Attachments.Add ("C:\test.txt")


End With
On Error GoTo 0


.Close savechanges:=False
End With

Set OutMail = Nothing


Kill TempFilePath & TempFileName & FileExtStr


End If
Next sh


Set OutApp = Nothing


With Application
.ScreenUpdating = True
.EnableEvents = True
End With
End Sub

 

[FormR]

Hi, I don't have outlook so can't do any testing - but you could try removing or commenting out this line:

On Error Resume Next

To see if it's hiding any issues.

 

[malek_ali7sas]

nothing change

 

[FormR]

Does the "signature" variable get set with what you expect? You can test be stepping through the code with F8 and using the immediate window

? signature

After these lines of code have executed.

If Dir(SigString) <> "" Then
Signature = .GetBoiler(SigString)
Else
Signature = ""
End If

Or you could add a msgbox after those lines.

msgbox signature