-
Jan 3rd, 2018, 11:04 AM
#1
Thread Starter
Hyperactive Member
[RESOLVED] Add signature to emails
Hi..I have the code below to email out reports. I would like to add my signature at the bottom on the email. Can someone please help. Thanks
Code:
Sub Email()
mypath = "C:\Reports\"
Set oboutlook = CreateObject("outlook.application")
For Each cel In Range("a2:a500")
If IsEmpty(cel) Then Exit For
Set nm = oboutlook.CreateItem(0) ' new mail item
With nm
.To = cel.Offset(, 1)
.Attachments.Add mypath & cel & ".xlsx"
.Body = "Please find attached reports"
.Subject = "Reports"
.Save
.Send
' .Close
End With
Next
End Sub
-
Jan 3rd, 2018, 11:54 AM
#2
Re: Add signature to emails
Originally Posted by fusion001
Hi..I have the code below to email out reports. I would like to add my signature at the bottom on the email. Can someone please help. Thanks
Code:
Sub Email()
mypath = "C:\Reports\"
Set oboutlook = CreateObject("outlook.application")
For Each cel In Range("a2:a500")
If IsEmpty(cel) Then Exit For
Set nm = oboutlook.CreateItem(0) ' new mail item
With nm
.To = cel.Offset(, 1)
.Attachments.Add mypath & cel & ".xlsx"
.Body = "Please find attached reports" & vbcrlf & _
vbcrlf & _
"Your Name" & vbcrlf & _
"Your Address" & vbcrlf & _
"Phone/Email/Whatever else you want to include" & vbcrlf & _
"And so on"
.Subject = "Reports"
.Save
.Send
' .Close
End With
Next
End Sub
Something like the bolded above should give you an idea of what you need to do.
-
Jan 3rd, 2018, 01:10 PM
#3
Thread Starter
Hyperactive Member
Re: Add signature to emails
I ideally would like to add the signature from outlook rather than hard code it.
-
Jan 3rd, 2018, 01:22 PM
#4
Re: Add signature to emails
-
Jan 3rd, 2018, 03:31 PM
#5
Re: Add signature to emails
looks like you just have to read the content of the saved signature file (from the default location in appdata) and add it to the body, as text, in your existing code, or html
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jan 4th, 2018, 06:32 AM
#6
Thread Starter
Hyperactive Member
Re: Add signature to emails
I added this to the code but it has not worked.
Code:
Sub Email()
Dim FileNameHTMSig As Variant
mypath = "C:\Reports\"
Set oboutlook = CreateObject("outlook.application")
For Each cel In Range("a2:a500")
If IsEmpty(cel) Then Exit For
Set nm = oboutlook.CreateItem(0) ' new mail item
With nm
.To = cel.Offset(, 1)
.Attachments.Add mypath & cel & ".xlsx"
.Body = "Please find attached reports"
.Subject = "Reports"
DirSig = ("C:\Users\" & Environ$("USERNAME") & "\AppData\Roaming\Microsoft\Signatures")
FileNameHTMSig = Dir$(DirSig & "\*.htm")
.Save
.Send
' .Close
End With
Next
End Sub
-
Jan 4th, 2018, 08:51 AM
#7
Re: Add signature to emails
Code:
DirSig = ("C:\Users\" & Environ$("USERNAME") & "\AppData\Roaming\Microsoft\Signatures")
FileNameHTMSig = Dir$(DirSig & "\*.htm")
All that did was build up the path and get a list of the files at that location. Nothing more. You have to then figure out which file at that location you want, OPEN it, READ the contents, PUT the contents into the email, which may or may not work, and then send the email.
-tg
-
Jan 4th, 2018, 03:09 PM
#8
Re: Add signature to emails
if the content of your signature file is in htm code you will need to convert the htm to text, as you are not using .htmlbody, htm will not be rendered
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jan 5th, 2018, 03:55 AM
#9
Thread Starter
Hyperactive Member
Re: Add signature to emails
I'm struggling with the code to get the signature. I could with some help please. Thanks
-
Jan 5th, 2018, 06:37 AM
#10
Re: Add signature to emails
i don't have any signatures setup in outlook, so can not test some code
have a look at
https://www.rondebruin.nl/win/s1/outlook/signature.htm
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jan 5th, 2018, 09:48 AM
#11
Re: Add signature to emails
I have 2016.
This is how I indicate the path of my signature file:
Code:
sigString = Environ("appdata") & "\Microsoft\Signatures\Signature.htm"
I have co-workers whose file is called "Main.htm" instead of signature, so you'll have to check what yours is called.
This calls a function to get the contents of that file:
Code:
Signature = GetBoiler(sigString)
This is the function:
Code:
Function GetBoiler(ByVal sFile As String) As String
Dim fso As Object
Dim ts As Object
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.GetFile(sFile).OpenAsTextStream(1, -2)
GetBoiler = ts.readall
ts.Close
End Function
As others have mentioned, you'll need to convert your email body one way the other. Since you only have one line, I'd suggest converting the body to HTML, so you could then insert "Signature" after the body.
-
Jan 5th, 2018, 09:54 AM
#12
Re: Add signature to emails
Follow up:
To convert your one line "body" to HTML (where "msg" is a string variable):
Code:
msg = "<font size='3'>Please find attached reports</font><br><br>"
To use the above, and add the signature:
Code:
.htmlbody = msg & Signature
-
Jan 5th, 2018, 10:43 AM
#13
Thread Starter
Hyperactive Member
Re: Add signature to emails
Thanks for that.
My signature is in this folder C:\Users\fred\AppData\Roaming\Microsoft\Signatures\fred.htm
The name fred will change depending on the user who runs the code. Is there a way to pick this up?
-
Jan 5th, 2018, 11:59 AM
#14
Re: Add signature to emails
Nope... not really because the name depends on the name of hte signature, not the user. I have 3... "Short", "Short-short", and "Full" ... the one I use depends on settings...
Just about the only way would be if you had an Options screen some where. Then you could list the signatures they have and let them select the one they want. Or no signature.
-tg
-
Jan 5th, 2018, 12:25 PM
#15
Re: Add signature to emails
Or, if they don't have multiple signatures, or just want to use the first one found, you could do something like this:
Code:
Sub getSig()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim type3 As String
Dim mySigFile As String
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(Environ("appdata") & "\Microsoft\Signatures")
For Each objFile In objFolder.Files
type3 = LCase(Left(objFile.Type, 3))
If type3 = "htm" Then
mySigFile = objFile.Name
Exit For
End If
Next objFile
End Sub
This would give you "Fred.htm" most likely, which you could append to the path in post #11.
(Or, as TG suggests, if they want to be more sophisticated, you'd have to find all the signature files, and let them choose.)
-
Jan 5th, 2018, 03:35 PM
#16
Re: Add signature to emails
if there are multiple signatures, you could have a listbox or similar for the user to select from, then save that selection for future use, but you might then need some option to change the signature at a later date
or you could have all the users setup a specifically named signature for your app
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jan 5th, 2018, 03:46 PM
#17
Thread Starter
Hyperactive Member
Re: Add signature to emails
Originally Posted by vbfbryce
Or, if they don't have multiple signatures, or just want to use the first one found, you could do something like this:
Code:
Sub getSig()
Dim objFSO As Object
Dim objFolder As Object
Dim objFile As Object
Dim type3 As String
Dim mySigFile As String
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder(Environ("appdata") & "\Microsoft\Signatures")
For Each objFile In objFolder.Files
type3 = LCase(Left(objFile.Type, 3))
If type3 = "htm" Then
mySigFile = objFile.Name
Exit For
End If
Next objFile
End Sub
This would give you "Fred.htm" most likely, which you could append to the path in post #11.
(Or, as TG suggests, if they want to be more sophisticated, you'd have to find all the signature files, and let them choose.)
Will I need to have a new sub in addition to the main one in post 4? Thanks
-
Jan 5th, 2018, 04:28 PM
#18
Re: Add signature to emails
You could do it either way. Include it in the main, or maybe create a function to return the file name, and call it from the main.
-
Jan 6th, 2018, 01:20 AM
#19
Re: Add signature to emails
as you are sending plain text emails you should use the file fred.txt rather than fred.htm for your signature, else the htm code will show in the email, if you want to send a more detailed signature, perhaps with a picture you should change to a html email body
this is a simple example to insert a signature with picture into a html email
Code:
Set msg = ol.CreateItem(0)
msg.HTMLBody = "this is a signature test <br>"
Open Environ("appdata") & "\Microsoft\Signatures\untitled.htm" For Input As 1
strsig = Input(LOF(1), #1)
Close 1
msg.HTMLBody = msg.HTMLBody & strsig
msg.Display
the displayed email included the initial string and the signature with pictue
unfortunately i did not give a name to the signature when i created it, hence untitled
if i was using msg.body i would have inserted the text from untitled.txt instead
if you want to allow the user to select from whatever signatures he has
you can use code like
Code:
f = Dir(Environ("appdata") & "\Microsoft\Signatures\*.htm")
If Len(f) = 0 Then MsgBox "no signatures"
Do While Len(f) > 0
lst.AddItem f
f = Dir
Loop
where lst is a listbox
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Jan 8th, 2018, 05:44 AM
#20
Thread Starter
Hyperactive Member
Re: Add signature to emails
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|