1

I have just started to use VBA in order to send emails in batches and I just have a problem because there is an extra line between the last sentence and the signature.

This is how it looks:

Dear Mr. Example 2,

message 1

message 2

Congratulations!

Space

Space

Signature

I have done a research in different forums but I couldn't find any solution to it.

Thank you in advance for your support!

This is the code I'm using:

Option Explicit
Sub Example()
   Dim olApp As Object
   Dim olMail As Object
   Dim olRecip As Object
   Dim olAtmt As Object
   Dim iRow As Long
   Dim Recip As String
   Dim Subject As String
   Dim Atmt As String


   iRow = 2

   Set olApp = CreateObject("Outlook.Application")

   Do Until IsEmpty(Cells(iRow, 1))

      Recip = Cells(iRow, 1).Value
      Subject = Cells(iRow, 3).Value
      Atmt = Cells(iRow, 4).Value ' Attachment Path


      Set olMail = olApp.CreateItem(0)

      With olMail
         Set olRecip = .Recipients.Add(Recip)
        .Display
        .Subject = Subject
        .HTMLbody = "<html><body><p>Dear " & Cells(iRow, 2).Value & "," & "<br>" & "<br>" & "message 1" & "<br>" & "<br>" & "message 2" & "<br>" & "<br>" & "Congratulations!" & .HTMLbody
         Set olAtmt = .Attachments.Add(Atmt)
         olRecip.Resolve
        .Save
        .Close 1


      End With

      iRow = iRow + 1

   Loop

   Set olApp = Nothing
   Exit Sub

End Sub'
ana
  • 11
  • 1
  • hi, i dont think thats the problem, but why are you adding .HTMLbody at the end of .HTMLbody = "

    .....

     – Luis Curado Sep 13 '18 at 15:07
  • the extra lines should come from the signature, check if in the signature have blanck lines before the text. – Luis Curado Sep 13 '18 at 15:08
  • Thank you Luis! I have to add the .HTMLbody at the end or otherwise the signature does not appear in the message. I have also checked my signature and there are no blank lines before the text. – ana Sep 14 '18 at 09:36
  • If u make a blank mail, Just with the. Htmlbody, the blank lines apear? – Luis Curado Sep 14 '18 at 12:25

1 Answers1

0

I believe I posted an answer today that would help resolve this, you can find it here - https://stackoverflow.com/a/70268202/17609669

The code to get the signature in this deletes the white space and then normal breaks can be used to provide the desired spacing.

Dharman
  • 30,962
  • 25
  • 85
  • 135
procopypaster
  • 416
  • 1
  • 6