Ancient Origin
New Member
- Joined
- Aug 11, 2022
- Messages
- 1
- Office Version
- 2019
- Platform
- Windows
- Mobile
Hello Team,
.
I am a new bie who can do research but doesn't know coding. I am good in making Logics and know initially what my end result should be like. I am making a VBA Code in which new Customer's Information will get copeid below the Old Customer's Information. that is also gets done automatically. I have made the VBA Code of Sending Email to the Customer by copying and joinging someone's else work and tested it as well thats its working. Once Email is Marked to the Customer, i get the "Mail Sent" Value in "I" Column. This is what Segregates the Old Customer from the New Customer. Now Since we have already marked an email to the Old Customer, i want to send email only to the new customer automatically without any manual intervention. So please help me if I need to make a Loop or If Logic can work here. Also in H Column I want to connect it with Logic, If by any chance it gives me result of "Duplicate". Logic Will End by not sending the email to the customer. Coding I have joined so far and is working on my PC is mentioned below :
.
I am a new bie who can do research but doesn't know coding. I am good in making Logics and know initially what my end result should be like. I am making a VBA Code in which new Customer's Information will get copeid below the Old Customer's Information. that is also gets done automatically. I have made the VBA Code of Sending Email to the Customer by copying and joinging someone's else work and tested it as well thats its working. Once Email is Marked to the Customer, i get the "Mail Sent" Value in "I" Column. This is what Segregates the Old Customer from the New Customer. Now Since we have already marked an email to the Old Customer, i want to send email only to the new customer automatically without any manual intervention. So please help me if I need to make a Loop or If Logic can work here. Also in H Column I want to connect it with Logic, If by any chance it gives me result of "Duplicate". Logic Will End by not sending the email to the customer. Coding I have joined so far and is working on my PC is mentioned below :
VBA Code:
Option Explicit
Sub Send_Email()
ActiveWorkbook.Save
Dim OutApp As Object
Dim OutMail As Object
Dim SigString As String
Dim Signature As String
Dim Path As String
Dim inam As String
Dim i As Integer
Dim lastrow As Integer
Set OutApp = CreateObject("Outlook.Application")
lastrow = Application.WorksheetFunction.CountA(Sheet6.Range("E:E"))
For i = 2 To lastrow
Path = Sheet6.Range("F" & i)
inam = Sheet6.Range("G" & i)
Set OutMail = OutApp.CreateItem(0)
SigString = "SIGNATURE PATH OF OUTLOOK"
If Dir(SigString) <> "" Then
Signature = GetBoiler(SigString)
Else
Signature = ""
End If
On Error Resume Next
With OutMail
.To = Sheet6.Range("E" & i).Value
.BCC = "FIXED BACK UP EMAIL ID"
.Subject = "AUTOMATED : INVOICE No. " & Sheet6.Range("B" & i).Value
.HTMLBody = "<BODY style = font-size:11pt; font-familt:Cambria>" & _
"INFORMATION TO THE CUSTOMER - PLEASE IGNORE THIS"
.Attachments.Add Path & inam & ".pdf"
.Send
End With
Sheet6.Range("I" & i).Value = "Mail Sent"
Next i
MsgBox "Mail Sent"
On Error GoTo 0
Set OutMail = Nothing
Set OutApp = Nothing
End Sub
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
